UC知道sql跨表更新
来源：百度知道 编辑：UC知道 时间：2024/06/30 05:57:01
b 表：id sender receiver mote choose confirmed
b表有sender=aa，receiver=bb，mote=1，choose=1 confirmed=1
更新a表中usernamme为aa，bb两条记录mote的值
假设a表中有两条记录
username mote
aaaaa 1
bbbbb 1
b表有
id sender receiver mote choose confirmed
1 aaaaa bbbbb 1 1 1
如果b表有sender=aaaaa，receiver=bbbbb，mote=1，choose=1 confirmed=1的记录，
就把a表中username字段为aaaaa和bbbbb记录的mote值改为2
分成两个语句更新也行，先更新aaaaa，再更新bbbbb
update a set mote = 2
where exists(select * from b where sender=aaaaa and receiver=bbbbb and mote=1 and choose=1 and confirmed=1) and username in ('aaaaa','bbbbb')
update b set mote = 2
where exists(select * from b where sender=aaaaa and receiver=bbbbb and mote=1 and choose=1 and confirmed=1)
这两个表之间的关系？
“bb两条记录mote的值”，这句什么意思？
----
update a set mote = 2
where exists (select 'x' from a a1, b b1 where (a1.username = b1.receiver or a1.username = b1.sender) and (a.username = b1.sender or a.username = b1.receiver))
如果是SQL Server数据库，这里的第一个表a，一定不要取别名！
用这一条语句就可以了。
update a
set mote = b.mote
from a join b
on a.username = b.sender
where b.sender = 'aa'
update a
set mote = b.mote
from a join b
on a.username = b.receiver
where b.receiver = 'bb'
意思是根据B表中的sender和receiver表的值更新aa表么？
update a set mote = (select