Forum Stats

  • 3,759,244 Users
  • 2,251,515 Discussions
  • 7,870,550 Comments

Discussions

Update logic

cubeguy
cubeguy Member Posts: 304 Bronze Badge
edited Sep 7, 2021 3:01PM in SQL & PL/SQL

Version 11.2.4

create table t1 (id number ,c1 varchar2(10),c2 varchar2(10),c3 varchar2(10),c4 varchar2(10),c5 varchar2(10),c6 varchar2(10),c7 varchar2(10),c8 varchar2(10),c9 varchar2(10),c10 varchar2(10));


insert into t1 values (100,'T11','T12','T13','T14','T15','T16','T17','T18','T19','T110');


create table t2 (oid number,c1 varchar2(10),c2 varchar2(10),c3 varchar2(10),c4 varchar2(10),c5 varchar2(10),c6 varchar2(10),c7 varchar2(10),c8 varchar2(10),c9 varchar2(10),c10 varchar2(10));


insert into t1 values (1,'T21','T22','T23',null,null,'T26','T27',null,'T29','T210');


I want to update table t1 using t2.


Logic is :

update t1 set c1 = (select c1 from t2 where oid =1 and c1 is not null) and id = 100;

update t1 set c2 = (select c2 from t2 where oid =1 and c2 is not null) and id = 100;

update t1 set c3 = (select c3 from t2 where oid =1 and c3 is not null) and id = 100;

update t1 set c4 = (select c4 from t2 where oid =1 and c4 is not null) and id = 100;

update t1 set c5 = (select c5 from t2 where oid =1 and c5 is not null) and id = 100;

update t1 set c6 = (select c6 from t2 where oid =1 and c6 is not null) and id = 100;

update t1 set c7 = (select c7 from t2 where oid =1 and c7 is not null) and id = 100;

update t1 set c8 = (select c8 from t2 where oid =1 and c8 is not null) and id = 100;

update t1 set c9 = (select c9 from t2 where oid =1 and c9 is not null) and id = 100;

update t1 set c10 = (select c10 from t2 where oid =1 and c10 is not null) and id = 100;


I need above all update statements to a single update statement

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,743 Red Diamond
    edited Sep 7, 2021 2:13PM Accepted Answer

    Now question is what do you want to do if there is no row in t2 with oid = 1? Update I posted will set t1 columns "c" to null. If you want them unchanged use:

    update t1
       set (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10) = (
                                               select  nvl(t2.c1,t1.c1),
                                                       nvl(t2.c2,t1.c2),
                                                       nvl(t2.c3,t1.c3),
                                                       nvl(t2.c4,t1.c4),
                                                       nvl(t2.c5,t1.c5),
                                                       nvl(t2.c6,t1.c6),
                                                       nvl(t2.c7,t1.c7),
                                                       nvl(t2.c8,t1.c8),
                                                       nvl(t2.c9,t1.c9),
                                                       nvl(t2.c10,t1.c10)
                                                 from  t2
                                                 where t2.oid = 1
                                              )
     where id = 100
       and exists (select 1 from t2 where oid = 1);
    
    

    SY.

Answers

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond
    edited Sep 7, 2021 1:51PM

    All your update statements are syntactically incorrect - they end with a condition, ... and id = 100. What does that mean - did you mean to have WHERE instead of AND? Or what else?

    Note that your test data is all in table t1. Did you mean the second insert to be to table t2?

    Please explain IN WORDS, not in code, what the update is supposed to do. I read what you wrote and I still don't understand the task.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,743 Red Diamond
    edited Sep 7, 2021 2:02PM
    SQL> select * from t1;
    
            ID C1    C2    C3    C4    C5    C6    C7    C8    C9    C10
    ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
           100 T11   T12   T13   T14   T15   T16   T17   T18   T19   T110
    
    SQL> select * from t2;
    
           OID C1    C2    C3    C4    C5    C6    C7    C8    C9    C10
    ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
             1 T21   T22   T23               T26   T27         T29   T210
    
    SQL> update t1
      2     set (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10) = (
      3                                             select  nvl(t2.c1,t1.c1),
      4                                                     nvl(t2.c2,t1.c2),
      5                                                     nvl(t2.c3,t1.c3),
      6                                                     nvl(t2.c4,t1.c4),
      7                                                     nvl(t2.c5,t1.c5),
      8                                                     nvl(t2.c6,t1.c6),
      9                                                     nvl(t2.c7,t1.c7),
     10                                                     nvl(t2.c8,t1.c8),
     11                                                     nvl(t2.c9,t1.c9),
     12                                                     nvl(t2.c10,t1.c10)
     13                                               from  t2
     14                                               where t2.oid = 1
     15                                            )
     16   where id = 100;
    
    1 row updated.
    
    SQL> select * from t1;
    
    
            ID C1    C2    C3    C4    C5    C6    C7    C8    C9    C10
    ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
           100 T21   T22   T23   T14   T15   T26   T27   T18   T29   T210
    
    SQL>
    

    SY.

  • EdStevens
    EdStevens Member Posts: 28,470 Gold Crown

    Cubeguy -

    Please learn to format your code: https://community.oracle.com/tech/apps-infra/kb/articles/12-format-text

    And aside from using the text formatting tools of this forum, you'd be well advised to learn to do a little formatting of your own, instead of writing your sql as one hot mess of run-on code.

    What do you think is easier to read (and debug)...

    This:

    create table t1 (id number ,c1 varchar2(10),c2 varchar2(10),c3 varchar2(10),c4 varchar2(10),c5 varchar2(10),c6 varchar2(10),c7 varchar2(10),c8 varchar2(10),c9 varchar2(10),c10 varchar2(10));
    

    or this

    CREATE TABLE t1 
      ( 
         id  NUMBER, 
         c1  VARCHAR2(10), 
         c2  VARCHAR2(10), 
         c3  VARCHAR2(10), 
         c4  VARCHAR2(10), 
         c5  VARCHAR2(10), 
         c6  VARCHAR2(10), 
         c7  VARCHAR2(10), 
         c8  VARCHAR2(10), 
         c9  VARCHAR2(10), 
         c10 VARCHAR2(10) 
      ); 
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,743 Red Diamond
    edited Sep 7, 2021 2:13PM Accepted Answer

    Now question is what do you want to do if there is no row in t2 with oid = 1? Update I posted will set t1 columns "c" to null. If you want them unchanged use:

    update t1
       set (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10) = (
                                               select  nvl(t2.c1,t1.c1),
                                                       nvl(t2.c2,t1.c2),
                                                       nvl(t2.c3,t1.c3),
                                                       nvl(t2.c4,t1.c4),
                                                       nvl(t2.c5,t1.c5),
                                                       nvl(t2.c6,t1.c6),
                                                       nvl(t2.c7,t1.c7),
                                                       nvl(t2.c8,t1.c8),
                                                       nvl(t2.c9,t1.c9),
                                                       nvl(t2.c10,t1.c10)
                                                 from  t2
                                                 where t2.oid = 1
                                              )
     where id = 100
       and exists (select 1 from t2 where oid = 1);
    
    

    SY.

  • cubeguy
    cubeguy Member Posts: 304 Bronze Badge

    Update the values of t1 columns (c1...c10) with t2 columns (c1..10) only if t2 c1..c10 columns are not null.

    I've updated the update statement in original post. Final output should be

    SQL> select * from t1;
    
    
            ID C1    C2    C3    C4    C5    C6    C7    C8    C9    C10
    ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
           100 T21   T22   T23   T14   T15   T26   T27   T18   T29   T210
    


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond

    Hi, @cubeguy

    I've updated the update statement in original post. Final output should be

    Please don't change your messages after you post them. It makes the thread hard to read, and it discourages people from helping you. Post all corrections or additions in a new reply at the end of the thread.

    In the sample data you posted, both INSERT statements add rows to t1. Should one of those statements say INSERT INTO t2 instead?

    What's wrong with the solution Solomon pasted at 10:11 (Eastern Time)? Point out where it is worng, and explain how you figure the correct results in those places.