How to update a column of date type assigned with the latest value in MySQL/MariaDB-CodePudding

I'm dealing with my MariaDB via Spring Boot's JPA dependency.

I have a table that looks like the one described as below

Field	Type	Null	Key	Default	Extra
id	varchar(32)	NO	PRIMARY	NULL	-
name	varchar(256)	YES	-	NULL	-
date	datetime(3)	NO	PRIMARY	NULL	-
ip	varchar(40)	NO	PRIMARY	NULL	-

Thus, id, date, and ip fields are the composite primary key for this table.

And I was trying to update the date of the latest one out of matching result using the query like

UPDATE my_table SET date = NOW() WHERE
   id = 'finding_id' AND
   ip = 'finding_ip';

Since there were duplicated id-ip paired entries in the data like following,

id	name	date	ip
finding_id	finding_name	2022-03-28 17:27:39.617	finding_ip
finding_id	finding_name	2022-03-28 16:52:01.483	finding_ip
another_id1	another_name1	2022-03-28 16:29:39.552	another_ip1
another_id2	another_name2	2022-03-28 16:29:24.435	another_ip2

I've got an error message.

As a makeshift, I appended another restriction to the where clause to narrow the range of time to be like "in last 15 minutes".

But I'm looking for more elegant workaround for this problem.

CodePudding user response：

In MySQL and MariaDB you can use ORDER BY and LIMIT in a update statement:

UPDATE my_table 
SET `date` = NOW() 
WHERE
   id = 'finding_id' AND
   ip = 'finding_ip'
ORDER BY `date` DESC
LIMIT 1;

Above will update the record with the highest date, within the filter because of the WHERE-clause, with the current datetime.

NOTE: It's better not to use any of the RESERVED WORDS as a column name. You can, but then you have to use backticks around the fieldname.

CodePudding user response：

The issue here is the database schema.

You have a primary key which is a UNIQUE composite key of 3 columns and you want to be able to have non-unique sets in them.

Which unless you want to update the Date to a new made up one everytime (adding/substracting miliseconds) is not possible.

I would recommend since you don't seem to care for uniqueness of the 3 columns, change the index type from PRIMARY to INDEX (still composite), so you don't have the UNIQUE constraint.

After that add a new "real" id column primary_id (or rename your other one, so the new one can be id) column which is just a normal PRIMARY KEY with Auto Increment turned on.