×

Loading...
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

@Calgary

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

  • 现在社区游泳池招救生员巡逻，过一会还要不停的点人头，这工作是否很快就被AI代替了，装一个摄像头后面是AI处理系统，这样只招一个等着救人就行了，人头沉下水一定时间立马强光照射，还可以分析水下人的状况来判断是否溺水等
  • 新冠疫苗引发心肌炎? 最新研究这样看
  • 这道几何题有解吗?
  • SAM 2 Demo
  • 如何在 AWS EC2 实例上构建 WordPress 应用程序?
    • 枫下论坛主坛 /  工作学习 /  IT技术讨论