MySQL Date TypesMySQL LAST_INSERT_IDMySQL InsertMySQL SelectMySQL WhereMySQL Group ByMySQL Order ByMySQL LimitMySQL LikeMySQL ISNULLMySQL BetweenMySQL JoinMySQL Left JoinMySQL Right JoinMySQL Cross JoinMySQL Self JoinMySQL Sub QueryMySQL UpdateMySQL DeleteMySQL ExplainMySQL IfMySQL Full-Text SearchMySQL INFORMATION_SCHEMAMySQL ConnectionMySQL Server Helpful FunctionsMySQL ViewMySQL Stored ProcedureMySQL FunctionsMySQL TransactionMySQL TriggerMySQL EventMySQL DeadlockMySQL DumpMySQL Reset Root PasswordMySQL interview – Advanced QueriesMySQL JsonMySQL BinlogMySQL Closure Table
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;
MySQL Date TypesMySQL LAST_INSERT_IDMySQL InsertMySQL SelectMySQL WhereMySQL Group ByMySQL Order ByMySQL LimitMySQL LikeMySQL ISNULLMySQL BetweenMySQL JoinMySQL Left JoinMySQL Right JoinMySQL Cross JoinMySQL Self JoinMySQL Sub QueryMySQL UpdateMySQL DeleteMySQL ExplainMySQL IfMySQL Full-Text SearchMySQL INFORMATION_SCHEMAMySQL ConnectionMySQL Server Helpful FunctionsMySQL ViewMySQL Stored ProcedureMySQL FunctionsMySQL TransactionMySQL TriggerMySQL EventMySQL DeadlockMySQL DumpMySQL Reset Root PasswordMySQL interview – Advanced QueriesMySQL JsonMySQL BinlogMySQL Closure Table