mysql merge the same data and keep the updated one later

user_17699980 0
Viewed 71

Table Structure

CREATE TABLE `goods` (
  `id` int NOT NULL ,
  `name` varchar(25) ,
  `updateat` datetime 
)

Now there is a piece of data like this in the table

Image

When I want to insert a new piece of data like (1,'new','2021-12-18 12:00:00').First determine whether there is the same data in the table as the data to be inserted (except for the update time),Then compare the update time, keep the latest piece of data.

I want to know how to use sql to achieve this function.

mysql sql
2 Answers

First add primary key to your table:

CREATE TABLE goods (
  id int NOT NULL ,
  name varchar(25) ,
  updateat datetime,
  PRIMARY KEY (id, name)
)

Then use ON DUPLICATE KEY UPDATE :

insert into goods values (1, 'john', '2021-01-02');
insert into goods (id, name, updateat)
 values (1, 'john', '2021-01-03')
 ON DUPLICATE KEY UPDATE 
 updateat = greatest('2021-01-03', 
                     (select updateat from (select * from goods as g) as g where id = 1))

Fiddle

user_176450990

Mysql uses INSERT ON...DUPLICATE instead of merge. This statement allows you to make modifications in your case an update based on a check when duplicate entries are identified. Duplicate entries may be identified using primary keys or unique indexes. The demo below and working db fiddle gives an example of this based on your criteria.

  1. Using a unique index to identify duplicate entries
  2. Using INSERT ON...DUPLICATE with the VALUES (used to identify currently inserted values) and a case expression to determine which updatedat date is more recent.
CREATE TABLE `goods` (
  `id` int NOT NULL ,
  `name` varchar(25) ,
  `updateat` datetime 
);

✓

-- use a unique index if you are interested in ensuring a subset of columns are unique and 
-- these columns do not meet the criteria to be a primary/composite key based on your database design
create unique index uk_id_name on  goods(id,name);

✓

insert into goods values (1,'new','2021-12-18 12:00:00');

✓

-- this should fail because of the duplicate unique index
insert into goods values (1,'new','2021-12-18 12:00:00');

Duplicate entry '1-new' for key 'uk_id_name'

select * from goods;

id | name | updateat           
-: | :--- | :------------------
 1 | new  | 2021-12-18 12:00:00

insert into goods values (1,'new','2021-12-18 12:00:01')
on duplicate key update updateat= CASE 
   WHEN updateat > VALUES(updateat) THEN updateat
   ELSE VALUES(updateat)
END;

✓

select * from goods;

id | name | updateat           
-: | :--- | :------------------
 1 | new  | 2021-12-18 12:00:01

db<>fiddle here

user_43071360
Related