Skip to content

Instantly share code, notes, and snippets.

@indera-shsp

indera-shsp/mysql update with exists and limit

Created January 11, 2019 18:35
Show Gist options
  • Save indera-shsp/e48ac7d7a8aaf81a9a4c6589a28bd4d1 to your computer and use it in GitHub Desktop.
Save indera-shsp/e48ac7d7a8aaf81a9a4c6589a28bd4d1 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
mysql update with exists and limit
-- ==============
-- Update with limit
-- ==============
drop table if exists customer;
drop table if exists supplier;
create table customer (
id int not null AUTO_INCREMENT,
customer_name varchar(10),
PRIMARY KEY (id)
);
create table supplier (
id int not null AUTO_INCREMENT,
supplier_name varchar(10),
PRIMARY KEY (id)
);
INSERT INTO customer (customer_name) VALUES ('aa'), ('bb');
INSERT supplier (supplier_name) VALUES ('xx'), ('yy');
select * from customer;
select * from supplier;
-- explain
UPDATE supplier s
SET supplier_name = (SELECT c.customer_name
FROM customer c
WHERE c.id = s.id AND s.supplier_name != c.customer_name)
WHERE EXISTS (SELECT *
FROM customer c
WHERE c.id = s.id AND s.supplier_name != c.customer_name)
LIMIT 1
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment