Let’s say for example you have a product table which stores information about products and a productPrice table which has pricing information and you want to update the prices based on when the product was created (in the examples below you want to discount all your older stuff to 80% of the current price).
 
In MySQL you can do this in one of two ways. The first is do do a join using commas, like so:
UPDATE product p, productPrice pp
SET pp.price = pp.price * 0.8
WHERE p.productId = pp.productId
AND p.dateCreated < ‘2004-01-01’
The second way is to use inner join syntax as shown below. This syntax is slightly more flexible as it means you can use left and right joins as well as inner joins, whereas the previous example is an implicit inner join.
UPDATE product p
INNER JOIN productPrice pp
ON p.productId = pp.productId
SET pp.price = pp.price * 0.8
WHERE p.dateCreated < ‘2004-01-01’
Another example of doing this in MySQL but using a left join to flag all the products that do not have assocated entries in the productPrice table is as follows:
UPDATE product p
LEFT JOIN productPrice pp
ON p.productId = pp.productId
SET p.deleted = 1
WHERE pp.productId IS null
 
This isn’t necessarily a very realistic example as you would be more likely to want to straight out delete the non-matching entries or use a select query instead to display them in a list for editing but it illustrates how it can be done. The article about how to delete records with MySQL using a cross join uses these examples to delete instead of update.
Note that it’s also possible in MySQL to update values in all the tables that are joined in the query. In this example we’re doing the same query as in the first two examples but also updating the “dateUpdated” value in the product table.
 
UPDATE product p
INNER JOIN productPrice pp
ON p.productId = pp.productId
SET pp.price = pp.price * 0.8,
p.dateUpdate = CURDATE()
WHERE p.dateCreated < ‘2004-01-01’
(CURDATE() is the MySQL function to return the current system date. For other date and time functions in MySQL refer to the MySQL date and time functions manual page).
 
UPDATE _vldmembers_data_members d
INNER JOIN _vldmembers members
ON d.data_id = members.member_id
INNER JOIN badrecords bad
ON members.username = bad.username
SET d.data_ethnicity = 275
WHERE members.username = bad.username;
 
SELECT * FROM _vldmembers_data_members d
INNER JOIN _vldmembers members
ON d.data_id = members.member_id
INNER JOIN asians
ON members.username = asians.data_ethnicity
 
UPDATE _vldmembers_data_members d
INNER JOIN _vldmembers members
ON d.data_id = members.member_id
INNER JOIN asians
ON members.username = asians.data_ethnicity
SET d.data_ethnicity = 274
WHERE members.username = asians.data_ethnicity;
 
UPDATE _vldmembers_data_members d
INNER JOIN _vldmembers members
ON d.data_id = members.member_id
SET d.data_ethnicity = 274
WHERE members.username LIKE ‘%asian’
 
‘asian%’
‘%azn’
‘azn%’
‘%azn%’
‘%asian%’
 
UPDATE _vldmembers_data_members d
INNER JOIN _vldmembers members
ON d.data_id = members.member_id
SET d.data_ethnicity = 274
WHERE members.username LIKE ‘%azn%’;
Categories: