This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

Ad by

推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

Thanks, ladies and gentlemen! 不知啥原因，只有cyt(千山万水走遍) 的帖子有"Reply" link. 只好在这里一并谢各位了。

fenix(肉树临风)

I've just tried it in Oracle.

cloud2001(卷云溶月)'s suggestion will assign NULL to record(cat='b' && type=1) as in the result shown below:

SQL> select * from tableA;
C TYPE VAL
- ---------- ----------
a 1 1000
a 2 110
b 1
b 2 201
c 1 2020
c 2 421
6 rows selected.

But thank cloud2001(卷云溶月) anyway.

As for cyt(千山万水走遍)'s suggestion, I couldn't find out what the correct syntax should be. When I tried the following:

UPDATE tableA A
SET A.val= (
SELECT DECODE(A.type,1,B.val1,2,B.val2)
FROM tableA A, tableB B
WHERE B.cat=A.cat)

Oracle complained, "single-row subquery returns more than one row".

Any tips, cyt(千山万水走遍) ?

I'll start try yangn(Raymond)'s suggestion in MS SQL Server. (to be continue)

(#688432@0)
Last Updated: 2002-8-12
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / IT技术讨论 / SQL question (Oracle, MS SQL Server) -fenix(肉树临风); 2002-8-12 {1373} (#687984@0)
本文发表在 rolia.net 枫下论坛I have two tables with structure and data as follows:

create tableA
(cat char(1), type number, val number);
------
'a', 1, 100
'a', 2, 110
'b', 1, 511
'b', 2, 201
'c', 1, 310
'c', 2, 421

create tableB
(cat char(1), val1 number, val2 number);
------
'a', 1000, 1234
'c', 2020, 2101

I want tableA to be updated according to corresponding records in tableB:
tableA (after updated)
------
'a', 1, 1000
'a', 2, 1234
'b', 1, 511
'b', 2, 201
'c', 1, 2020
'c', 2, 2101

So I wrote the following PL/SQL script for Oracle:

update tableA A
set A.val = (select B.val1 from tableB B where B.cat = A.cat)
where A.cat in (select cat from tableB)
and A.type = 1;
update tableA A
set A.val = (select B.val2 from tableB B where B.cat = A.cat)
where A.cat in (select cat from tableB)
and A.type = 2;

But I don't feel it is the decent answer.
Do you have a better solution? What if column(tableA.type) has a large value space (1, 2, ..., N) and tableB has many columns (val1, val2, ..., valN)?

And what's the answer for the same question in MS SQL Server?

Thanks in advance!更多精彩文章及讨论，请光临枫下论坛 rolia.net

My suggestion -cloud2001(卷云溶月); 2002-8-12 {311} (#688047@0)
In 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.
给个sql server 的solution:(见内) column增多,无非增加相应的case 而已,依然是single update statement. -yangn(Raymond); 2002-8-12 {291} (#688130@0)
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还是应该尽量不用.

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)
I've just tried it in Oracle.

cloud2001(卷云溶月)'s suggestion will assign NULL to record(cat='b' && type=1) as in the result shown below:

SQL> select * from tableA;
C TYPE VAL
- ---------- ----------
a 1 1000
a 2 110
b 1
b 2 201
c 1 2020
c 2 421
6 rows selected.

But thank cloud2001(卷云溶月) anyway.

As for cyt(千山万水走遍)'s suggestion, I couldn't find out what the correct syntax should be. When I tried the following:

UPDATE tableA A
SET A.val= (
SELECT DECODE(A.type,1,B.val1,2,B.val2)
FROM tableA A, tableB B
WHERE B.cat=A.cat)

Oracle complained, "single-row subquery returns more than one row".

Any tips, cyt(千山万水走遍) ?

I'll start try yangn(Raymond)'s suggestion in MS SQL Server. (to be continue)

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)
SQL> UPDATE tableA A SET A.val= ( SELECT DECODE(A.type,1,B.val1,2,B.val2) FROM tableB B WHERE B.cat=
A.cat);
6 rows updated.
SQL> select * from tableA;
C TYPE VAL
- ---------- ----------
a 1 1000
a 2 1234
b 1
b 2
c 1 2020
c 2 2101
6 rows selected.

I changed it to

UPDATE tableA A
SET A.val= (
SELECT DECODE(A.type,1,B.val1,2,B.val2)
FROM tableB B
WHERE B.cat=A.cat
)
WHERE A.cat in (SELECT cat FROM tableB)

then

4 rows updated.
SQL> select * from tableA;
C TYPE VAL
- ---------- ----------
a 1 1000
a 2 1234
b 1 511
b 2 201
c 1 2020
c 2 2101
6 rows selected.

Done. Thanks a lot!

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)

@Las Vegas

Thanks, ladies and gentlemen! 不知啥原因，只有cyt(千山万水走遍) 的帖子有"Reply" link. 只好在这里一并谢各位了。

Replies, comments and Discussions:

More Topics