select one record each from two table. update the one value by matching the another one to a new table

Question

i am having three tables, like table1,table2,table3..

and i need a mysql solution for.

select table1.val1,table2.val1 from table1,table2 and update the returned values like..

update table3 set value=table1.val1 where value=table2.val1

my query not working..

update db1.proprietor_profile_tbl as c3 set c3.PPROFILE_profileid=(select c2.COM_id from db2.company_profile_tbl as c1,db1.company_profile_tbl as c2 where c1.COM_name=c2.COM_name and COM_profiletype!=4) where c3.PPROFILE_profileid=c1.COM_id

Unknown column 'c1.COM_id' in 'where clause'

John Woo John Woo · Accepted Answer · 2014-01-25T08:07:57

UPDATE  table3 t3
        INNER JOIN table2 t2
            ON t3.pcomid = t2.comid
        INNER JOIN table1 t1
            ON t1.name=t2.name
SET     t3.pcomid = t1.comid

select one record each from two table. update the one value by matching the another one to a new table

1 Answers