MySQL Update

 

The UPDATE clause is used to update an existing row. It can be used to update a single row, a group of rows, or all rows within a table.

UPDATE 
    table_name1, table_name2,... 
SET 
    column_name1 = value1,
    column_name2 = value2,
    ...
WHERE conditions;

How to use UPDATE
1. Specify the tables you want to update.
2. Set the column values to their new values.
3. The WHERE clause is optional but in most cases required. If you forget the WHERE clause, your UPDATE will affect every row which is something you might not want.

 


Let’s update the row with id 2.

We updated the first_name and last_name of the row with id 2 to Folau and Kaveinga. Don’t be confused that we have two queries above. One for the update and the other is a SELECT query which is used to check our update.

UPDATE with LOW_PRIORITY

LOW_PRIORITY tells MySQL to perform the update after all reading from any connection to the table is done.

UPDATE with IGNORE

IGNORE tells MySQL to ignore an error in the update. If errors occur then ignore those rows and continue til the end.

Our example above updated rows 2 and 4 even though ids 100 and 101 don’t exist.

How to update a table column by values return from a SELECT statement

Here we are updating the last_name of the customer by the last_name of the employee whose id is 2.

How to update a table from a table in another database but in the same server instance.

SET SQL_SAFE_UPDATES=0;

UPDATE

	db_account.user as acctUser, db_billing.user as billingUser
    
SET 
	
    billingUser.name = acctUser.name

WHERE 
	
    acctUser.id = billingUser.id;

SET SQL_SAFE_UPDATES=1;

Here we have a db_account database and db_billing database that live in the same server. And we are updating the user name of the db_billing.user by the user name of the db_account.user.

UPDATE with no KEY

MySQL will not allow you to use UPDATE without a WHERE clause or a LIMIT clause. MySQL will not allow you to use WHERE without including the id column in the WHERE condition.

These queries will not run because the first query does not use a WHERE clause. The second query does not have a id check condition.

UPDATE user SET name = 'Folau';
or
UPDATE user SET name = 'Folau' WHERE email = 'test@gmail.com';

MySQL uses SQL_SAFE_UPDATES as being set to 0 to disable this checking.

SET SQL_SAFE_UPDATES=0;

It is always good practice to turn SQL_SAFE_UPDATES back to on right after.

SET SQL_SAFE_UPDATES=1;

Bulk UPDATE

Sometimes you are tasked to update database records which are too much to write individual UPDATE queries for.

UPDATE user SET name = 'Folau' WHERE id=1;
UPDATE user SET name = 'Lisa' WHERE id=2;
UPDATE user SET name = 'Kinga' WHERE id=3;
...

The above queries can be 10000+ rows which will perform very slow to execute.

What you can do is use the case statement

UPDATE user 
SET name = 
  (CASE id WHEN 1 THEN 'Folau'
           WHEN 2 THEN 'Lisa'
           WHEN 3 THEN 'Kinga'
   END)
WHERE id IN (1,2,3);

And you can have 10000+ WHEN THEN clauses and the query will perform better than individual UPDATEs.

Bulk UPDATE with multiple columns

You can update multiple columns all at the same time.

UPDATE with JOIN

UPDATE account as acct
  LEFT JOIN member as memb ON memb.account_id = acct.id
  LEFT JOIN group as grp ON grp.uuid = memb.group_uuid
SET acct.group_uuid = memb.group_uuid,
  acct.partner_slug = grp.url_slug,
  acct.paid_by = grp.paid_by
WHERE acct.id <> 0 
  AND memb.group_uuid IS NOT NULL 
  AND acct.group_uuid IS NULL;

 




Subscribe To Our Newsletter
You will receive our latest post and tutorial.
Thank you for subscribing!

required
required


Leave a Reply

Your email address will not be published.