This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / SQL question (Oracle, MS SQL Server)
-fenix(肉树临风);
2002-8-12
{1373}
(#687984@0)
-
My suggestionIn Oracle:
1. update tableA A
set A.val = (select B.val1 from tableB B where B.cat = A.cat)
where A.type = 1;
2. for more columns, create a store procedure, declare a cursor, use Dynamic SQL: EXECUTE IMMEDIATE 'sql statement', change the sql statement dynamically to do it;
I don't know SQL Server.
-cloud2001(卷云溶月);
2002-8-12
{311}
(#688047@0)
-
给个sql server 的solution:(见内)
column增多,无非增加相应的case 而已,依然是single update statement.update tableA
set tableA.val=
case
tableA.type
when 1
then tableB.val1
when 2
then tableB.val2
end
from tableA,tableB where tableA.cat=tableB.cat
不敢说是oracle的专家,但二位好象都没有能用单一statement去完成update.无论是SQL SERVER还是ORACLE,能不用cursor还是应该尽量不用.
-yangn(Raymond);
2002-8-12
{291}
(#688130@0)
-
PF, PF
-cleveland(alex);
2002-8-14
(#691798@0)
-
Since I have no testing Environment. Following answer are only for your reference.
Oracle:
UPDATE A SET value= SELECT DECODE(A.type,1,B.val1,2,B.val2,...) FROM B WHERE B.cat=A.cat;
SQL: CASE statement should work.
-cyt(千山万水走遍);
2002-8-12
(#688329@0)
-
Thanks, ladies and gentlemen! 不知啥原因，只有cyt(千山万水走遍) 的帖子有"Reply" link. 只好在这里一并谢各位了。
-fenix(肉树临风);
2002-8-12
{880}
(#688432@0)
-
decode前面加上distinct试试
-hugefox(长得象熊的狐狸);
2002-8-12
(#688531@0)
-
Still rejected by Oracle, "single-row subquery returns more than one row". It seems the semantics of "UPDATE table SET column=value" in Oracle is different from that in MS SQL Server.
-fenix(肉树临风);
2002-8-12
(#688705@0)
-
Try this one:
UPDATE tableA A
SET A.val= (
SELECT DECODE(A.type,1,B.val1,2,B.val2)
FROM tableB B
WHERE B.cat=A.cat)
-cyt(千山万水走遍);
2002-8-12
(#688870@0)
-
Almost done. Thanks a lot! Good night
-fenix(肉树临风);
2002-8-12
{845}
(#688897@0)
-
yangn(Raymond)'s suggestion runs perfectly in MS SQL Server. Thanks!
-fenix(肉树临风);
2002-8-12
(#688462@0)
-
You are welcome.
-yangn(Raymond);
2002-8-12
(#688483@0)