UPDATE 一对多 的 从多中选出一个记录更新的问题！难！！！！谢谢！！！大虾多多帮忙！！！！
表A
ID SQ NAME
001 01 CHENG
表B
ID SQ SHUXU NAME
001 01 1 LI
001 01 2 ZHANG
如何用一个语句把表B中 SHUXU 较大的 记录的 NAEM 更新到 表A 中对应的记录的NAME 字段,(表A与表B 用 ID SQ 对应)
即把表A 中的NAME 值更新为 ZHANG
谢谢!!!!大虾多多帮忙啊!!!!!!!!
[解决办法]
update a
set
NAME=b.NAME
from
表A a,
表B b
where
a.ID=b.ID
and
a.SQ=b.SQ
and
not exists(select 1 from 表B where ID=b.ID and SQ=b.SQ and SHUXU> b.SHUXU)
[解决办法]
update 表A
set a.NAME=b.NAME
from 表A a,(select ID,SQ,NAME from 表B t where not exists(select * from 表B where ID=t.ID and SQ=t.SQ and SHUXU> t.SHUXU))b
where a.ID=b.ID and a.SQ=b.SQ
[解决办法]
update a
set
NAME=b.NAME
from
表A a,表B b
where
a.ID=b.ID
and
a.SQ=b.SQ
and
b.SHUXU=(select max(SHUXU) from 表B where ID=b.ID and SQ=b.SQ)
[解决办法]
update A set Name = B.Name from B join (select ID,SQ,max(SHUXU) SHUXU from B group by ID,SQ) aa on aa.SHUXU =B.SHUXU
[解决办法]
update A set A.NAME=B.NAME from A,B where B.SHUXU in(select
max(B.SHUXU)from B group by ID)
[解决办法]
update A set A.NAME=B.NAME from A,B where B.SHUXU in(select
max(B.SHUXU)from B group by ID)
刚测试了,可以的
