Informix

nested-group-icon.png

DB2

Expand all | Collapse all

14.10.FC4W1 - update + from

  • 1.  14.10.FC4W1 - update + from

    Posted Fri January 15, 2021 07:49 AM

    Hi, 

    Does anyone know if its safe to use the update + from syntax on version 14.10? 
    There is no documentation and no mention in "what news",  however the syntax below works! 

    create temp table tp1 ( cod int , nome char(10)) with no log ;

    create temp table tp2 ( cod int , nome char(10)) with no log ;

     

    insert into tp1 select tabid, tabname from systables ;

    insert into tp2 select tabid, '' from systables ;

     

    select * from tp2 where cod = 100

     

    update tp1

    set nome = tp2.nome

    from tp2

    where tp2.cod = tp1.cod

      and tp2.cod = 100

     



    ------------------------------
    Cesar Martins
    ------------------------------


  • 2.  RE: 14.10.FC4W1 - update + from

    Posted Fri January 15, 2021 09:18 AM
    So you're asking whether the explicit subselect is optional and the following is not only working (it is!), but supported (though not documented)?

    create temp table tp1 ( cod int , nome char(10)) with no log ;
    create temp table tp2 ( cod int , nome char(10)) with no log ;
    
    insert into tp1 select tabid, tabname from systables ;
    insert into tp2 select tabid, 'test_string' from systables ;
    
    select * from tp1 where cod = 100;
    
    update tp1
       set nome = -- (select
                  tp2.nome
                      from tp2
     where tp2.cod = tp1.cod
       and tp2.cod = 100
                  -- )
    ;
    
    select * from tp1 where cod = 100;
    ​

    A question for SQL gods to weigh in...

    ------------------------------
    Andreas Legner
    ------------------------------



  • 3.  RE: 14.10.FC4W1 - update + from

    Posted Fri January 15, 2021 09:57 AM
    Hi Andreas, 

    Hmmm, no, what I would like to know is if the syntax below is safe and supported, perhaps not documented, I want to stop using updates+subselects and merge as a workaround. Waiting for this resource for a loooooooong time.
    If you ran my little script on 14.10 you will see they work. 

    update tp1

    set nome = tp2.nome

    from tp2

    where tp2.cod = tp1.cod

      and tp2.cod = 100



    ------------------------------
    Cesar Martins
    ------------------------------