MYSQL - How to update a table from a sub query with 2 columns-CodePudding

I am using MYSQL and i have a query with multiple joins in it but the end result is that query produces 2 columns. I wish to update a column in a table based on those 2 columns.

Example, my query with the all the joins produces the below results with the following 2 columns.

column1:    column2:  
john          23        
Lisa          45        
Tim           56        
etc.......

The total rows returned is about 100. The table i'm trying to update has 3 columns in it. I want to update column 3 based only on the combination of the 2 rows that my query produces. I know the below doesn't work but just using it to illustrate what i'm trying to do.

update table1
set column3 = valuex
where column1 and column2
in
(this is my query with the joins that produces the 2 columns referenced above)

This is where i want to say 'apply this update only based on the rows with the combination of the 2 columns of my original query'

Hope i worded my question clearly enough:) Thanks !!

CodePudding user response：

I think you merely got the syntax for the IN clause wrong:

update table1
set column3 = @valuex
where (column1, column2) in ( <your query here> );

CodePudding user response：

update table1 set column3 = @valuex where (column1, column2) in (select column1, column2 from ( )

CodePudding user response：

Use the multi-table syntax:

UPDATE table1
    JOIN t2  ON ...
    JOIN t3  ON ...
    SET  table1.col3 = valx;