flower flower - 6 months ago 8
SQL Question

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
)

Answer

You can try this simple query

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