CODEWITHSUNDEEP
mysqlsql
flower
flower

Reputation: 2242

How to update a field of a table that is inner join of another table in MySQL?

I have two tables: tablea and tableb. Both of them have three columns called columna, columnb ,columnc.

Now I want to do this:

If tablea.columnb equals tableb.columnb,then set tablea.columnc = tableb.columnc. I have written the sql and it works well, but I think that there must be a better way to do this? Can anyone help me to optimize my sql statement, or is there any other way? 

UPDATE tablea ta 
SET 
ta.columnc = (
              SELECT columnc FROM tableb
              WHERE ta.columnb = tableb.columnb
             )
WHERE ta.columnb IN (
                     SELECT columnb FROM tableb
                     WHERE ta.columnb = tableb.columnb
                    )

Upvotes: 0

Views: 30

Answers (1)

AssenKhan
AssenKhan

Reputation: 566

You can try this simple query

Update tablea ta,table tb set ta.columnc=tb.columnc where ta.columnb =tb.columnb;

Upvotes: 1

Related Questions