none
Update using mutliple Table values functions RRS feed

  • Question

  • I have a table with
    id int identity
    number varchar(50)
    status int

    and 2 Table values functions
    each one return different numbers
    table 1 :
    123
    564
    125
    table 2:
    444
    567
    321

    when i do updatei want to update the status based on the table it exist in
    of it's table1 then status=1
    if table 2 then status=2
    currentl i do it in 2 differnt queries for example :

    UPDATE S
    SET S.Status = 1,
    FROM [dbo].[Logs] S
    WHERE EXISTS (select prefix from [dbo].[table1] () g where s.ToNumber like g.prefix+'%')


    Thursday, August 29, 2019 3:36 PM

All replies

  • It looks like this is what you want to do, but I am unsure what table2 is.

    UPDATE S
    SET S.Status = case when g.prefix is not null 1 else 2 end  
    FROM [dbo].[Logs] S
    left join [dbo].[table1] g on  s.ToNumber like g.prefix+'%'
    

    Thursday, August 29, 2019 3:51 PM
  • create table test (id int identity,
    number int,
    status int)
    insert into test (number)values(123),(564),(125),(444),(567),(321),(999)
    
    create table table1 ( 
    number int )
    insert into table1 (number)values(123),(564),(125)
    create table table2 ( 
    number int )
    insert into table2 (number)values (444),(567),(321) 
    Update t
    Set Status=
     
     Case when  t1.number is not null then 1 
      when  t2.number is not null then 2 
    else null end
    from test t 
    left join table1 t1 on t.number=t1.number
    left join table2 t2 on t.number=t2.number
    
    
    select * from test
    
    drop table test,table1,table2

    Thursday, August 29, 2019 4:08 PM
    Moderator
  • >> I have a table [sic] with
    id int identity
    number varchar(50)
    status int <<

    Why were you so rude is to fail to post DDL? Did you know that a table has to have a key? Did you know that an identity table property (it is not a column!) can never be a key by definition? Why do you think that element names like "number" which are storing is a string are useful to anyone? Since a status code has to be the status of something in particular, that name is fundamentally wrong. Since the status is on the nominal scale it cannot be a numeric data type. You've never had a course or read a book on data modeling. So now we have to do all the work that you couldn't do for yourself
     
    CREATE TABLE Alphas
    (foobar_string CHAR(3) NOT NULL PRIMARY KEY,
     foo_status CHAR(1));  -- has to be null-able

    INSERT INTO Alphas(foobar_str)
    VALUES ('125'), ('564'), ('125');

    CREATE TABLE Betas
    (foobar_string CHAR(3) NOT NULL PRIMARY KEY,
     foo_status CHAR(1));

    INSERT INTO Betas(foobar_str)
    VALUES ('444'), ('567'), ('321'); 

    >> when I do update I want to update the status based on the table it exist in
    of it's table1 then status=1 if table 2 then status=2
    currently I do it in 2 different queries for example :

    UPDATE S
    SET S.Status = 1,
    FROM [dbo].[Logs] S
    WHERE EXISTS (SELECT prefix FROM [dbo].[table1] () AS G WHERE  S.to_number LIKE G.prefix+'%');
    <<

    How can you update columns that do not exist in the tables? Why are you using the old Sybase UPDATE… FROM… syntax which is known to do things incorrectly because of cardinality errors? Could you please try again and post something that makes sense?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, August 29, 2019 10:40 PM
  • Hi ,

    Thank you for your posting,

     

    Could you  please share us your table structure (CREATE TABLE...)and some sample data (INSERT INTO...) along with your expected result? So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 30, 2019 2:03 AM