MySQL Update multiple table instance

MySQL Update multi-table example explanation

Let's take a look at the following examples:

Example one:

Update TAB1 set TAB1. Product size = (select TAB2. Product size from TAB2 where tab2. Product color = tab1. Product color) where TABL1. Product color in (select TAB2. Product Colors From TAB2)

Continue with the following examples:

UPDATE cms_document a,cms_template B SET a.ruletype2=b.ruletype WHERE A.templateid = B.templateid

An alternative approach may be to use:

Update customers A--use alias set CITY_NAME=NVL (select B.city_name from tmp_cust_city b where b.customer_id=a.customer_id) . City_name)

Or

Set CITY_NAME=NVL (select B.city_name from tmp_cust_city b where b.customer_id=a.customer_id), ' unknown '

MySQL multiple table update instance three:

Update Contact C, contactdroit CD set c.user_name = ' $username ', c.nom = ' $lastname ', c.prenom = ' $firstname ', C.passcode = ' $password ', cd.droit_id = ' $droitid ' where c.contact_id = ' $id ' and c.contact_id = cd.contact_id;

MySQL Association multiple tables for update operations

UPDATE Track INNER JOIN MV on Track.trkid=mv.mvid SET track.is_show=mv.is_show WHERE trkid<6

Equal to

UPDATE track,mv SET track.is_show=mv.is_show WHERE track.trkid=mv.mvid and Trkid<6

Example four:

UPDATE Product P INNER JOIN productprice pp on p.productid = pp.productid SET pp.price = pp.price * 0.8,p.dateupdate = CUR DATE () WHERE p.datecreated < ' 2004-01-01 '

Note : Please pay attention to the triple programming Tutorials section for more wonderful articles .