flower flower - 1 year ago 59
SQL Question

How to update a filed of a table that is inner join on the other table in MySQL?

I have two table called tablea and tableb.Both of them have three columns called columna,columnb,columnc.Now I want to do this: if tablea.columnb equal tableb.columnb,then set tablea.columnc= tableb.columnc.I have write the sql and it work well.But I think that there must have a beeter sql to do this?Can anyone help me to optimize my sql statement or do there have the 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 Source

You can try this simple query

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