×

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

@Florida

SQL question (Oracle, MS SQL Server)

fenix(肉树临风)
本文发表在 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
(#687984@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)

More Topics

  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
    • 枫下论坛主坛 /  工作学习 /  IT技术讨论