CODEWITHSUNDEEP
mysqlsql
sean
sean

Reputation: 35

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

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 !!

Upvotes: 0

Views: 69

Answers (3)

Rick James
Rick James

Reputation: 142298

Use the multi-table syntax:

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

Upvotes: 0

Eric
Eric

Reputation: 21

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

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

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

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

Upvotes: 1

Related Questions