locked
SQL update with union all RRS feed

  • Question

  • User-352524747 posted

    I have two tables, both have Code as column. I may use UNION ALL to select Code from both tables.

    How to use UPDATE with UNION ALL to change the value from this selection?

    Monday, February 27, 2017 4:32 PM

Answers

  • User753101303 posted

    Hi,

    You could add a column in your UNION ALL query so that you know from which original table each row is coming from. Then you'll have to generate an UPDATE statement that will take that information into account so that it update the correct underlying table (or use this information in a stored procedure).

    If your db engine support that you could have a look if you have a "partitioned tables" feature.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 28, 2017 2:28 PM

All replies

  • User527778624 posted

    Hi,

    Try this:

    update t3 set t3.col2=tbl.code
    from table3 t3 inner join 
    (
    select id,code from table1
    union all
    select id,code from table2
    ) as tbl on t3.col1=tbl.id;
    

    Monday, February 27, 2017 5:29 PM
  • User-352524747 posted

    I have two tables. I want to update the value in any of the colums.

    Monday, February 27, 2017 11:16 PM
  • User527778624 posted

    Hi,

    How to use UPDATE with UNION ALL to change the value from this selection?

    AFAIK, It's not possible.

    selection with UNION ALL creates derived table, and try to update it won't effect your real table(s).

    Tuesday, February 28, 2017 7:30 AM
  • User-352524747 posted

    So to perform an update on either one or both tables what should i do?

    Should i first check the "where is" clause and than execute the sql or should i build a sql in one variable and split ; using a foreach to execute two queries?

    Tuesday, February 28, 2017 2:07 PM
  • User753101303 posted

    Hi,

    You could add a column in your UNION ALL query so that you know from which original table each row is coming from. Then you'll have to generate an UPDATE statement that will take that information into account so that it update the correct underlying table (or use this information in a stored procedure).

    If your db engine support that you could have a look if you have a "partitioned tables" feature.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 28, 2017 2:28 PM