mysql - Update a column in a table based on column in another table -

the tables 50k rows, , taking on hour run this. figure out way optimize query runs more quickly.

tbl1 - serviceid, userid

tbl2 - serviceid, userid, type

tbl1.serviceid values unique

tbl2.serviceid values not unique, come couple of different tables

tbl2.type equals either or b

(tbl2.serviceid, tbl2.userid, tbl2.type) unique, , set multi column primary key if needed

here code using in system updates:

$sql = "select * tbl1"; $rs = full_query ( $sql ); while ( $row = mysql_fetch_object( $rs ) ){     $sql = "update tbl2 set type='a' serviceid='" . $row->id . "' , userid='" . $row->userid . "'";     full_query( $sql );     $sql = "update tbl2 set userid='" . $row->userid . "'  type='a' , serviceid='" . $row->id . "'";     full_query($sql); } 

here ended using:

$sql = "update tbl2 t2 inner join tbl1 t1 on t1.id = t2.serviceid , t2.userid = t1.userid set t2.type = 'a'"; full_query( $sql ); $sql = "update tbl2 t2 inner join tbl1 t1 on t1.id = t2.serviceid , t2.type='a ' set t2.userid = t1.userid"; full_query( $sql ); 

if understand correctly, can solve 2 sql statements. doing same thing.

when doing sql development try keep in mind using set-based theory, , should try avoid loop as possible (procedural-based programming).

see more detail: http://www.codeproject.com/articles/34142/understanding-set-based-and-procedural-approaches

-- update type if [serviceid] , [userid] matches update t2 set t2.type = 'a' tbl2 t2     inner join tbl1 t1         on t1.id = serviceid         , t2.userid = tbl1.userid;  -- update user if [serviceid] , [type] = matches update t2 set t2.userid = tbl1.userid tbl2 t2      inner join tbl1 t1         on t1.id = serviceid t2.type = 'a'