在Update语句中给表指定别名的有关问题
在Update语句中给表指定别名的问题。
table1表中引用如下,下面2行数据是相互链接的 (parentid)
ID, Party1Id, Party2Id, ParentID
1, 11, 22,
2, 22, 11, 1
现在想把11和33链接,更新的结果应该如下:
ID, Party1Id, Party2Id, ParentID
1, 11, 33,
2, 33, 11, 1
语句出错:table1有2个!
UPDATE table1 SET Party1Id=33
FROM table1 T1 WHERE T1.ID = table1.ParentID AND T1.ID = 1
主要是table1没有办法在Update 语句中写起个别名!
------解决思路----------------------
可以这样写如下:
UPDATE table1 SET Party1Id=33 FROM (select ID, Party1Id, Party2Id, ParentID from table1)as T1 WHERE T1.ID = table1.ParentID AND T1.ID = 1
------解决思路----------------------
UPDATE b SET b.Party1Id=33
FROM table1 b,table1 a WHERE b.ID = a.ParentID AND b.ID = 1
table1表中引用如下,下面2行数据是相互链接的 (parentid)
ID, Party1Id, Party2Id, ParentID
1, 11, 22,
2, 22, 11, 1
现在想把11和33链接,更新的结果应该如下:
ID, Party1Id, Party2Id, ParentID
1, 11, 33,
2, 33, 11, 1
语句出错:table1有2个!
UPDATE table1 SET Party1Id=33
FROM table1 T1 WHERE T1.ID = table1.ParentID AND T1.ID = 1
主要是table1没有办法在Update 语句中写起个别名!
------解决思路----------------------
可以这样写如下:
UPDATE table1 SET Party1Id=33 FROM (select ID, Party1Id, Party2Id, ParentID from table1)as T1 WHERE T1.ID = table1.ParentID AND T1.ID = 1
------解决思路----------------------
UPDATE b SET b.Party1Id=33
FROM table1 b,table1 a WHERE b.ID = a.ParentID AND b.ID = 1