//应该是两者交换 id //procedure TInP_Server.TempletIDChange(nOpeID, nIID1, nIID2: Integer); procedure Server_TempletIDChange(nOpeID:Integer; nIID1, nIID2: string); var sql:string; begin sql := 'update [Bas_Templet_List] set vieworder= IID where ISNULL(vieworder, 0)=0'; //先给初始值 ExecuteSQL(sql);
sql := ' update [Bas_Templet_List] set vieworder2 = (SELECT vieworder FROM [Bas_Templet_List] WHERE iid = '+nIID2+') where iid=' + nIID1 + '; update [Bas_Templet_List] set vieworder2 = (SELECT vieworder FROM [Bas_Templet_List] WHERE iid = '+nIID1+') where iid=' + nIID2
INSERT INTO table1 (c1, c2, c3) (SELECT v1, v2, v3 FROM table2)
答案是可以的,具体的语法如下:
1 2 3 4 5 6
UPDATE table1 alias SET (column_name,column_name ) = ( SELECT (column_name, column_name) FROM table2 WHERE column_name = alias.column_name) WHERE column_name = VALUE
下面是这样一个例子: 两个表a、b,想使b中的memo字段值等于a表中对应id的name值 表a:
1 2 3 4
id name 1 王 2 李 3 张
表b:
1 2 3 4
id ClientName 1 2 3
(MS SQL Server)语句:
1
UPDATE b SET ClientName = a.name FROM a,b WHERE a.id = b.id
(Oralce)语句:
1
UPDATE b SET (ClientName) = (SELECT name FROM a WHERE b.id = a.id)
update set from 语句格式 当where和set都需要关联一个表进行查询时,整个update执行时,就需要对被关联的表进行两次扫描,显然效率比较低。