MySql provides an insert query to add records to the table, but with time, those records need to be updated. MySQL provides an update query. Update query syntax is as below
UPDATE tableName
SET column1 = value1,
column2 = value2,
column_n = value_n
[WHERE condition];Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
for safety purposes, by default Mysql doesn't allow updating of all the records of the table, it requires a where clause with the key column, to disable this turn off the SAFE_UPDATE.
SET SQL_SAFE_UPDATES = 0;Update All Rows
Mysql update query takes a filter or where parameter to specify which record needs to be updated, it's an optional parameter but if the where clause is not provided, then all the table records will be updated.
Update Single Column
Each UPDATE query will take a column name and corresponding value that will be updated in the respective record.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, total BIGINT, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1', 1);
INSERT INTO apple VALUES (2,"Fuji", 'N', '2022-02-2', 2);
INSERT INTO apple VALUES (3,"Gala", 'Y', '2022-03-3', 3);
INSERT INTO apple VALUES (4,"Jonagold", 'N', '2022-04-4', 4);
INSERT INTO apple VALUES (5,"GrannySmith", 'Y', '2022-05-5', 5);
SET SQL_SAFE_UPDATES = 0;
update apple set available_date = '2022-06-6';
select *from apple;some times, the old column value is required to update the column with the new value, such as incrementing the existing value by 1.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, total BIGINT, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1', 1);
INSERT INTO apple VALUES (2,"Fuji", 'N', '2022-02-2', 2);
INSERT INTO apple VALUES (3,"Gala", 'Y', '2022-03-3', 3);
INSERT INTO apple VALUES (4,"Jonagold", 'N', '2022-04-4', 4);
INSERT INTO apple VALUES (5,"GrannySmith", 'Y', '2022-05-5', 5);
SET SQL_SAFE_UPDATES = 0;
update apple set total = total + 1;
select * from apple;Update Multiple Column
UPDATE query can take a comma-separated list of columns with their corresponding value that will be updated in the respective record.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple VALUES (2,"Fuji", 'N', '2022-02-2');
INSERT INTO apple VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO apple VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple VALUES (5,"GrannySmith", 'Y', '2022-05-5');
update apple set available_date = '2022-06-6', available='Y';
select * from apple;Update Selected Rows
The WHERE clause specifies which row needs to be updated. it can match single or multiple columns.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple VALUES (2,"Fuji", 'N', '2022-02-2');
INSERT INTO apple VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO apple VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple VALUES (5,"GrannySmith", 'Y', '2022-05-5');
update apple set available_date = '2022-06-6' where apple_id =1;
select *from apple;The same WHERE clause can be modified to update multiple columns as well.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple VALUES (2,"Fuji", 'N', '2022-02-2');
INSERT INTO apple VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO apple VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple VALUES (5,"GrannySmith", 'Y', '2022-05-5');
update apple set available_date = '2022-06-6' where apple_id > 1;
select *from apple;Update Specific Numbers of Rows
MySql provides an optional LIMIT parameter to LIMIT the records update without specifying the WHERE clause.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple VALUES (2,"Fuji", 'N', '2022-02-2');
INSERT INTO apple VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO apple VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple VALUES (5,"GrannySmith", 'Y', '2022-05-5');
update apple set available_date = '2022-06-6' limit 2;
select * from apple;The LIMIT parameter can be combined with the WHERE clause to limit the records updating even WHERE clause has multiple matches.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple VALUES (2,"Fuji", 'N', '2022-02-2');
INSERT INTO apple VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO apple VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple VALUES (5,"GrannySmith", 'Y', '2022-05-5');
update apple set available_date = '2022-06-6' where apple_id > 1 limit 2;
select * from apple;Update Query Using SubQuery
A Subquery is a query inside another query. MySql allows subquery in place of either value or where clause. but it won't allow updating the same table which is used for SELECT in the subquery. if the below query is run, it will throw an error.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple VALUES (2,"Fuji", 'N', '2022-02-2');
INSERT INTO apple VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO apple VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple VALUES (5,"GrannySmith", 'Y', '2022-05-5');
update apple set available_date = (select max(available_date) from apple) where apple_id = 1;
select * from apple;Error Code: 1093. You can't specify target table 'TableName' for update in FROM clause
the above error is because the query is selecting and updating is the same, there might come a situation where the query is updating the same record from the value of the same record as shown below.
update apple set available_date = (select available_date from apple where apple_id = 1 ) where apple_id = 1;this statement will result in an inconsistent state of the table, hence Mysql doesn't allow it.
There are multiple ways to make it work, let's see those ones by one.
Subquery from Another Table
MySql will allow Subquery in update statement if the subquery is selecting the value from a different table as shown below
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple VALUES (2,"Fuji", 'N', '2022-02-2');
INSERT INTO apple VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO apple VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple VALUES (5,"GrannySmith", 'Y', '2022-05-5');
CREATE TABLE appleTmp (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO appleTmp VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO appleTmp VALUES (2,"Fuji", 'N', '2022-02-2');
INSERT INTO appleTmp VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO appleTmp VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO appleTmp VALUES (5,"GrannySmith", 'Y', '2022-05-5');
update apple set available_date = (select available_date from appleTmp where apple_id = 5 ) where apple_id = 1;
select * from apple;Subquery from Same Table with Alias
MySQL by default doesn't allow selection and update in the same table, the workaround for this is to wrap the inner select statement inside an outer select statement and the outer select will be given an alias ie appleTmp in this case.
MySQL will create a temporary alias table ie appleTmp from other select queries and appleTmp will be used as the source table.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, total BIGINT, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1', 1);
INSERT INTO apple VALUES (2,"Fuji", 'N', '2022-02-2', 2);
INSERT INTO apple VALUES (3,"Gala", 'Y', '2022-03-3', 3);
INSERT INTO apple VALUES (4,"Jonagold", 'N', '2022-04-4', 4);
INSERT INTO apple VALUES (5,"GrannySmith", 'Y', '2022-05-5', 5);
update apple set total = (select max(total) from (select total from apple) appleTmp) where apple_id = 1;
select * from apple;Update Rows in Order
Sometimes we need to update a column, by default MySql updates from start and proceeds down. If the column has a UNIQUE constraint, there might be a chance where 2 different rows might contain the same value which violates the UNIQUE constraint and the update will fail, let's see the below example
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, total BIGINT, PRIMARY KEY (apple_id), CONSTRAINT uqniue_apple_name UNIQUE (total));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1', 1);
INSERT INTO apple VALUES (2,"Fuji", 'N', '2022-02-2', 2);
INSERT INTO apple VALUES (3,"Gala", 'Y', '2022-03-3', 3);
INSERT INTO apple VALUES (4,"Jonagold", 'N', '2022-04-4', 4);
INSERT INTO apple VALUES (5,"GrannySmith", 'Y', '2022-05-5', 5);
SET SQL_SAFE_UPDATES = 0;
update apple set total = total + 1 ;
select * from apple;the total column is unique, Mysql will update the first-row total column to the current value +1 ie 2, but the second row already contains 2, which violates the UNIQUE constraint hence the update query will fail. In this scenario to avoid the UNIQUE constraint violation, the UPDATE should happen from the bottom.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, total BIGINT, PRIMARY KEY (apple_id), CONSTRAINT uqniue_apple_name UNIQUE (total));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1', 1);
INSERT INTO apple VALUES (2,"Fuji", 'N', '2022-02-2', 2);
INSERT INTO apple VALUES (3,"Gala", 'Y', '2022-03-3', 3);
INSERT INTO apple VALUES (4,"Jonagold", 'N', '2022-04-4', 4);
INSERT INTO apple VALUES (5,"GrannySmith", 'Y', '2022-05-5', 5);
SET SQL_SAFE_UPDATES = 0;
update apple set total = total + 1 ORDER BY apple_id DESC;
select * from apple;
