×

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

SQL question (Oracle, MS SQL Server)

本文发表在 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
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / SQL question (Oracle, MS SQL Server)
    本文发表在 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
      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.
      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
    • 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.
      • Thanks, ladies and gentlemen! 不知啥原因,只有cyt(千山万水走遍) 的帖子有"Reply" link. 只好在这里一并谢各位了。
        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试试
          • 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.
        • 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)
          • Almost done. Thanks a lot! Good night
            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!
        • You are welcome.