各位老大,求教一sql文table1: document
id userId orderId
1 null 1
2 null 1
3 null 2
table2: order
id userId
1 10
2 11
要求一个更新table1的sql脚本
把table1中的userId更新成table2中和orderId相对应的userId。结果
table1: document
id userId orderId
1 10 1
2 10 1
3 11 2
求指教哈。。急急急。
id userId orderId
1 null 1
2 null 1
3 null 2
table2: order
id userId
1 10
2 11
要求一个更新table1的sql脚本
把table1中的userId更新成table2中和orderId相对应的userId。结果
table1: document
id userId orderId
1 10 1
2 10 1
3 11 2
求指教哈。。急急急。
set userid = b.userid
from document a
inner join order b
on a.orderid = b.id
on table1
set table1.userID=table2.userID where table1.orderID=table2.orderID
update table1
set userid = table2.userId
from table1,table2
where table1.orderId= table2.id
create table document (id int ,userId varchar(10), orderId int)
insert into document select
1,'',1 union all select
2,'',1 union all select
3,'',2
create table [order](id int,userId int)
insert into [order] select
1,10 union all select
2,11 update document set userId=O.userId from [order] O join document d on D.orderId=O.[id]/*
id userId orderId
----------- ---------- -----------
1 10 1
2 10 1
3 11 2(所影响的行数为 3 行)
*/
from document a join order b
on a.orderid = b.id