update a set state='1' where exists (select * from b where a.checkno=b.rnumber and a.borrow=a.borow)
update a set state=1 from (select Rnumber,borrow from b group by Rnumber,borrow having count(id)>1) b where a.checkno=b.rnumber and a.borrow=b.borrow
update a set state=1 from (select Rnumber,borrow from b group by Rnumber,borrow having count(id)>1) bb where a.checkno=bb.rnumber and a.borrow=bb.borrow
UPDATE AA SET AA.state=1 FROM a AA inner join (SELECT CheckNo,Borrow FROM a GROUP BY CheckNo,Borrow HAVING COUNT(*) = (SELECT COUNT(*) FROM b WHERE a.CheckNo=b.Rnumber and a.Borrow=b.Borrow)) BB ON BB.CheckNo=AA.CheckNO AND BB.Borrow=AA.Borrow
UPDATE CC SET CC.state=1 FROM a CC inner join (select AA.CheckNo,AA.Borrow from (select CheckNo,Borrow,Count(*) as Count from a group by CheckNo,Borrow) AA INNER JOIN (select Rnumber,Borrow,Count(*) as Count from B group by Rnumber,Borrow) BB ON AA.CheckNo=BB.Rnumber and AA.Borrow=BB.Borrow and AA.Count = BB.Count) DD ON DD.CheckNo=CC.CheckNo AND DD.Borrow=CC.Borrow
and a.borrow=a.borow)
where a.checkno=b.rnumber and a.borrow=b.borrow
where a.checkno=bb.rnumber and a.borrow=bb.borrow
(SELECT CheckNo,Borrow FROM a GROUP BY CheckNo,Borrow
HAVING COUNT(*) = (SELECT COUNT(*) FROM b
WHERE a.CheckNo=b.Rnumber and a.Borrow=b.Borrow)) BB
ON BB.CheckNo=AA.CheckNO AND BB.Borrow=AA.Borrow
SET CC.state=1
FROM a CC inner join
(select AA.CheckNo,AA.Borrow from
(select CheckNo,Borrow,Count(*) as Count from a group by CheckNo,Borrow) AA
INNER JOIN (select Rnumber,Borrow,Count(*) as Count from B group by Rnumber,Borrow) BB ON AA.CheckNo=BB.Rnumber and AA.Borrow=BB.Borrow
and AA.Count = BB.Count) DD ON DD.CheckNo=CC.CheckNo
AND DD.Borrow=CC.Borrow
a
------------------
ID CheckNo Borrow state
1 1 100 0 <------相同 (只能update两个)
2 1 100 0 <------相同
3 3 50 0
4 7 20 0
5 1 100 0 <------相同
------------------b
------------------
ID Rnumber Borrow state
1 1 100 0 <------相同 (共两个)
2 3 30 0
3 1 100 0 <------相同
4 7 20 0
---------------------------符合条件: a.CheckNo=b.Rnumber and a.Borrow=b.Borrow
执行: 如果b表中有多少个符合条件的记录,那么在a表中就对多少个记录中的state值设1
------------------
ID CheckNo Borrow state
1 1 100 1
2 1 100 1
3 3 50 0
4 7 20 1
5 1 100 0
------------------或a
------------------
ID CheckNo Borrow state
1 1 100 1
2 1 100 0
3 3 50 0
4 7 20 1
5 1 100 1
------------------或a
------------------
ID CheckNo Borrow state
1 1 100 0
2 1 100 1
3 3 50 0
4 7 20 1
5 1 100 1
------------------
from a aa