Update Join

Databases
1

I have two tables
Table 1 Table 2
‘column1’ ‘column3_column1’
‘column2’ ‘column4’

column 3 is foreign key related to column1 which is primary key of Table1
I want to change value of column4 based on the value given in the query of column2
Example of Select Query

select
       t1.column2,
       t2.column4
from Table2 t2
join Table1 t1 on t2.column3 = t1.column1 and column2 = '551996-0';
2

One way to do this is to use CTE selecting data and use it in a later update. (pseudocode):

WITH data AS (select
       t1.column2,
       t2.column4
from Table2 t2
join Table1 t1 on t2.column3 = t1.column1 and column2 = '551996-0')

UPDATE table SET col4=data.col2 where ID=X

It can be done in other ways as well, but AFAIK you cannot UPDATE using JOIN

3

you can in MySQL

3 Likes
4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.