求两表批量更新sql语句
完成更新a表和b表，最好用一个sql语句完成，不可以的话就更新a表一个语句，更新b表一个语句。
基础是a表和b表的Category都一样为x
一、根据b表更新a表，要求：
1、若b表中存在与a表中aID相同的bID且aCheck是1就将aValidate更新为1，aCheck 不是1就将aValidate更新为3。
2、若b表中不存在与a表中aID相同的bID且aCheck是1就将aValidate更新为2，aCheck不是1就将aValidate更新为4。
二、根据a表更新b表，要求：
1、若a表中存在与b表中bID相同的aID且aCheck是1就将bValidate更新为1，aCheck 不是1就将bValidate更新为3。
2、若a表中不存在与b表中bID相同的aID且aCheck是1就将bValidate更新为2。
示例数据：
更新前
a： b:
aID aCheck Category aValidate bID Validate Category
1 1 x null 1 null x
2 1 x null 3 null x
6 2 x null 7 null x
7 3 x null 8 null x
8 3 x null 9 null x
9 1 y null 10 null y
更新后：
a：
aID aCheck aValidate Category
1 1 1(b表中存在且aCheck是1) x
2 1 2(b表中不存在且aCheck是1) x
6 2 3(b表中不存在且aCheck不是1) x
7 3 4(b表中存在且aCheck不是1) x
8 3 4(b表中存在且aCheck不是1) x
9 1 null(Category是y不变) y
b：
bID bValidate Category
1 1(a表中存在且aCheck是1) x
3 2(a表中不存在) x
7 3(a表中存在且aCheck不是1) x
8 3(a表中存在且aCheck不是1) x
9 2(a表中不存在) x
10 null(Catetory是y不更新) y
[解决办法]
create table a(aID int, aCheck int, Category varchar(2), aValidate int)
insert a select 1, 1, 'x ', null
insert a select 2, 1, 'x ', null
insert a select 6, 2, 'x ', null
insert a select 7, 3, 'x ', null
insert a select 8, 3, 'x ', null
insert a select 9, 1, 'y ', null
create table b(bID int, Validate int, Category varchar(2))
insert b select 1, null, 'x '
insert b select 3, null, 'x '
insert b select 7, null, 'x '
insert b select 8, null, 'x '
insert b select 9, null, 'x '
insert b select 10, null, 'y '
update a
set avalidate=case when a.acheck=1 then 1 else 3 end
from b join a on b.bid=a.aid
update a
set aValidate=case when acheck=1 then 2 else 4 end
where
not exists(select 1 from b where bid=a.aid)
update b
set Validate=case when acheck=1 then 1 else 3 end
from b join a on b.bid=a.aid
update b
set Validate=case when acheck=1 then 2 else Validate end--acheck不为1时不更新
from b left join a on a.aid=b.bid
where a.aid is null
select * from a
select * from b
aID aCheck Category aValidate
----------- ----------- -------- -----------
1 1 x 1
2 1 x 2
6 2 x 4
7 3 x 3
8 3 x 3
9 1 y 1
(6 行受影响)
bID Validate Category
----------- ----------- --------
1 1 x
3 NULL x
7 3 x
8 3 x
9 1 x
10 NULL y
(6 行受影响)
[解决办法]
create table a(aID int, aCheck int, Category varchar(10), aValidate int)
insert into a select 1, 1, 'x ', null
union all select 2, 1, 'x ', null
union all select 6, 2, 'x ', null
union all select 7, 3, 'x ', null
union all select 8, 3, 'x ', null
union all select 9, 1, 'y ', null
create table b(bID int, Validate int, Category varchar(10))
insert into b select 1, null, 'x '
union all select 3, null, 'x '
union all select 7, null, 'x '
union all select 8, null, 'x '
union all select 9, null, 'x '
union all select 10, null, 'y '
update a set aValidate = (case when a.acheck=1 and b.bid is not null then 1
when a.acheck=1 and b.bid is null then 2
when a.acheck <> 1 and b.bid is null then 3
when a.acheck <> 1 and b.bid is not null then 4 end)
from a full join b on b.bID=a.aID and b.Category= 'x '
where a.Category= 'x '
update b set Validate =(case when a.acheck=1 and a.aid is not null then 1
when a.aid is null then 2
when a.acheck <> 1 and a.aid is not null then 3
when a.acheck <> 1 and a.aid is null then 4 end)
from a full join b on b.bID=a.aID and a.Category= 'x '
where b.Category= 'x '
[解决办法]
--借用roy_88(中国风_燃烧你的激情！！！) 的數據
create table a(aID int, aCheck int, Category varchar(2), aValidate int)
insert a select 1, 1, 'x ', null
insert a select 2, 1, 'x ', null
insert a select 6, 2, 'x ', null
insert a select 7, 3, 'x ', null
insert a select 8, 3, 'x ', null
insert a select 9, 1, 'y ', null
create table b(bID int, Validate int, Category varchar(2))
insert b select 1, null, 'x '
insert b select 3, null, 'x '
insert b select 7, null, 'x '
insert b select 8, null, 'x '
insert b select 9, null, 'x '
insert b select 10, null, 'y '
GO
Update
A
Set avalidate = Case When B.bid Is Null Then
Case A.acheck When 1 Then 2 Else 4 End
Else
Case A.acheck When 1 Then 1 Else 3 End
End
From
A
Left Join
B
On A.aid = B.bid And A.Category = B.Category
Where A.Category = 'x '
Update
B
Set Validate = Case When A.aid Is Null
Then 2
Else
Case A.acheck When 1 Then 1 Else 3 End
End
From
B
Left Join
A
On A.aid = B.bid And A.Category = B.Category
Where B.Category = 'x '
Select * From A
Select * From B
GO
Drop Table A, B
--Result
/*
aIDaCheckCategoryaValidate
11x1
21x2
62x4
73x3
83x3
91yNULL
bIDValidateCategory
11x
32x
73x
83x
92x
10NULLy
*/
[解决办法]
update a
set avalidate=case when a.acheck=1 then 1 else 3 end
from b join a on b.bid=a.aid
update a
set aValidate=case when acheck=1 then 2 else 4 end
where
not exists(select 1 from b where bid=a.aid)
update b
set bValidate=case when acheck=1 then 1 else 3 end
from b join a on b.bid=a.aid
update b
set bValidate=case when acheck=1 then 2 else bValidate end
where
not exists(select 1 from a where aid=b.bid)
