有两个名为test1和test2的表
test1
Name ID Refnum
CHANDRA 1 1234
Vinay 2 1324
test2
Username Userid Usernum
A 10 1322
B 12 1221
1234
1324
当Refnum和Usernum相等时,我们必须分别用test1的name和id中的数据更新test2表的username和user id。需要对此进行查询
像这样?
UPDATE test2
SET Username = name and Userid = ID
FROM test1, test2
WHERE test1.Refnum = test2.Usernum
Shreyas Prakash给出的答案是正确的,但是查询中有语法错误。试试下面的查询
UPDATE test2
SET Username = name, Userid = ID
FROM test1, test2
WHERE test1.Refnum = test2.Usernum
在标准SQL中,您可以使用相关子查询:
update test2
set name = (select t1.name from test1 t1 where t1.refnum = test2.usernum),
userid = (select t1.userid from test1 t1 where t1.refnum = test2.usernum)
where name is null;
不同的数据库有快捷键,但是你没有指定你使用的是哪个数据库。