27,580
社区成员
发帖
与我相关
我的任务
分享100分, 如何求交集再更新
要求性能高一些. 语法可读性好些.
a表数据如下
aId qty dc wh ChkFlag note remark1
1 20 1 1 N NULL 1
3 20 1 2 n NULL 2
4 60 1 2 n NULL 3
5 20 1 4 n NULL 4
5 20 1 5 n NULL 5
B表数据如下:
bId qty dc op wh ChkFlag note remark2
3 20 1 2 1 N NULL 1
4 20 1 3 2 N NULL 2
5 -20 1 6 3 N NULL 3
8 20 1 2 4 N NULL 4
5 20 1 2 5 N NULL 5
要求: 求 A 表和b表两个表相同的数据 都a表和b表的 chkflag 设置为 y , 条件是:
aid = bid
a.dc = b.dc
a.wh = b.wh
a.qty = b.qty * op
如何写的更好些.
...全文
请发表友善的回复…
发表回复
發糞塗牆 2014-07-11
- 打赏
- 举报
UPDATE a
SET a.ChkFlag='Y'
FROM abcdeefsag as A
WHERE EXISTS (SELECT 1 FROM #ta ta WHERE a.aid=ta.aid AND a.qty=ta.qty AND a.dc=ta.dc AND a.wh=ta.wh AND a.chkflag=ta.chkflag AND a.note=ta.note AND a.remark1=ta.remark1)
要这样写
poloyzhang 2014-07-11
- 打赏
- 举报
如果A表的表名很名 能否写成类似如下:
UPDATE abcdeefsag as a
SET a.ChkFlag='Y'
FROM A
WHERE EXISTS (SELECT 1 FROM #ta ta WHERE a.aid=ta.aid AND a.qty=ta.qty AND a.dc=ta.dc AND a.wh=ta.wh AND a.chkflag=ta.chkflag AND a.note=ta.note AND a.remark1=ta.remark1)
业余草 2014-07-11
- 打赏
- 举报
高手写的代码就是不一样的，学习了
發糞塗牆 2014-07-11
- 打赏
- 举报
不加主键的后果就是搞死自己，一模一样就只能一起更新，SQL Server和人都无法判断出要更新哪一条
發糞塗牆 2014-07-11
- 打赏
- 举报
没有主键的话只能全表每一列都匹配才能唯一标识一列
IF OBJECT_ID('Tempdb..#ta','u')IS NOT NULL
DROP TABLE #ta
SELECT a.* INTO #ta
FROM a INNER JOIN b ON aid = bid AND a.dc = b.dc AND a.wh = b.wh AND a.qty = b.qty * op
IF OBJECT_ID('Tempdb..#tb','u')IS NOT NULL
DROP TABLE #tb
SELECT b.* INTO #tb
FROM a INNER JOIN b ON aid = bid AND a.dc = b.dc AND a.wh = b.wh AND a.qty = b.qty * op
UPDATE a
SET a.ChkFlag='Y'
FROM A
WHERE EXISTS (SELECT 1 FROM #ta ta WHERE a.aid=ta.aid AND a.qty=ta.qty AND a.dc=ta.dc AND a.wh=ta.wh AND a.chkflag=ta.chkflag AND a.note=ta.note AND a.remark1=ta.remark1)
UPDATE B
SET B.ChkFlag='Y'
FROM b
WHERE EXISTS (SELECT 1 FROM #tb tb WHERE b.bid=tb.bid AND b.qty=tb.qty AND b.dc=tb.dc AND b.wh=tb.wh AND b.chkflag=tb.chkflag AND b.note=tb.note AND b.remark2=tb.remark2 AND b.op=tb.op)
poloyzhang 2014-07-11
- 打赏
- 举报
而且还可能存在两条一模一样的行.
poloyzhang 2014-07-11
- 打赏
- 举报
注意 aid 和bid 不是唯一的. 不是主键. 其它的也不是.
發糞塗牆 2014-07-11
- 打赏
- 举报
不一定，假设索引全部合理、高效，第二个语句的结果集相对较少（这个例子中只有一条满足子查询，而第一个语句有5条），有可能第二个更高效
撇开楼主的这个效率高的写法，下面两条执行，问下版主，下面语句是不是第一个语句效率高些
update A表 set a.chkflag='y' from A表
inner join B表
on A表.aid= B表.aid and
A表.dc= B表.dc and
A表.wh= B表.wh and
A表.qty= B表.qty*op
update A表 set a.chkflag='y' where A表.Aid in
(select Aid from A表
inner join B表
on A表.aid= B表.aid and
A表.dc= B表.dc and
A表.wh= B表.wh and
A表.qty= B表.qty*op
)
霜寒月冷 2014-07-11
- 打赏
- 举报
撇开楼主的这个效率高的写法，下面两条执行，问下版主，下面语句是不是第一个语句效率高些
update A表 set a.chkflag='y' from A表
inner join B表
on A表.aid= B表.aid and
A表.dc= B表.dc and
A表.wh= B表.wh and
A表.qty= B表.qty*op
update A表 set a.chkflag='y' where A表.Aid in
(select Aid from A表
inner join B表
on A表.aid= B表.aid and
A表.dc= B表.dc and
A表.wh= B表.wh and
A表.qty= B表.qty*op
)發糞塗牆 2014-07-11
- 打赏
- 举报
由于UPDATE只能在一次中影响一个表，所以要把满足条件的数据预存起来，否者第二次update的时候数据已经变了update不了
發糞塗牆 2014-07-11
- 打赏
- 举报
cte只能用在接下来的一次UPDATE，用临时表可以
發糞塗牆 2014-07-11
- 打赏
- 举报
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-11 08:16:55
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([aId] int,[qty] int,[dc] int,[wh] int,[ChkFlag] varchar(1),[note] sql_variant,[remark1] int)
insert [A]
select 1,20,1,1,'N',null,1 union all
select 3,20,1,2,'n',null,2 union all
select 4,60,1,2,'n',null,3 union all
select 5,20,1,4,'n',null,4 union all
select 5,20,1,5,'n',null,5
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([bId] int,[qty] int,[dc] int,[op] int,[wh] int,[ChkFlag] varchar(1),[note] sql_variant,[remark2] int)
insert [b]
select 3,20,1,2,1,'N',null,1 union all
select 4,20,1,3,2,'N',null,2 union all
select 5,-20,1,6,3,'N',null,3 union all
select 8,20,1,2,4,'N',null,4 union all
select 5,20,1,2,5,'N',null,5
--------------开始查询--------------------------
IF OBJECT_ID('Tempdb..#t','u')IS NOT NULL
DROP TABLE #t
SELECT aid INTO #t
FROM a INNER JOIN b ON aid = bid AND a.dc = b.dc AND a.wh = b.wh AND a.qty = b.qty * op
UPDATE a
SET a.ChkFlag='Y'
FROM A INNER JOIN #t ON A.aId=#t.aId
UPDATE B
SET B.ChkFlag='Y'
FROM b INNER JOIN #t ON bId=#t.aId
SELECT * FROM a
SELECT * FROM b
----------------结果----------------------------
/*
aId qty dc wh ChkFlag note remark1
----------- ----------- ----------- ----------- ------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
1 20 1 1 N NULL 1
3 20 1 2 n NULL 2
4 60 1 2 Y NULL 3
5 20 1 4 n NULL 4
5 20 1 5 n NULL 5
(5 行受影响)
bId qty dc op wh ChkFlag note remark2
----------- ----------- ----------- ----------- ----------- ------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
3 20 1 2 1 N NULL 1
4 20 1 3 2 Y NULL 2
5 -20 1 6 3 N NULL 3
8 20 1 2 4 N NULL 4
5 20 1 2 5 N NULL
*/
poloyzhang 2014-07-11
- 打赏
- 举报
用CTE还是临时表? 还是其它. 我写在存储过程中用的.需要多次调用.
