Update records with a key pair

Question

I need to do update User table records that have the max endDate replaced for a user with the value of null. A null will indicate that the specific user record is the current record. To explain: this table tracks temporal changes for each user. Here is what I am doing right now. It works but the CONCAT could be intensive. Is there another way that would be faster then this? Cursors are definitely slower. Tried that already.

db is mysql

USE CUSTOMER;
CREATE TEMPORARY TABLE IF NOT EXISTS keyValuePairs
SELECT `user`, MAX(endDate)as mEndDate FROM `User` GROUP BY `user`;

UPDATE `User` SET `endDate` = NULL
WHERE CONCAT(`user`, `endDate`) IN 
(SELECT CONCAT(`user`, `mEndDate`) FROM keyValuePairs);

PinnyM · Accepted Answer · 2014-03-27T19:12:24.780

1

Even faster, you can avoid making an unneccesary temp table:

UPDATE `User` u
LEFT JOIN `User` maxUser ON u.user = maxUser.user AND maxUser.endDate > u.endDate
SET u.`endDate` = NULL
WHERE maxUser.user IS NULL;

This will set the endDate to NULL for any User row that has no corresponding User row with a greater endDate - which will be the maximum endDate for that user.

Working SQLFiddle here.

edited Mar 27 '14 at 19:12

answered Mar 27 '14 at 16:27

PinnyM

35,165
3
73
81

that looks nice...giving a try – Curt Mar 27 '14 at 16:31
I changed the UPDATE to be a SELECT COUNT(*) and kept the WHERE EXISTS, I compared this return with the count in my temp table and there are more in the temp table, which suggests im not updating everything I need. Maybe because if there is only 1 record for that user it wont be updated, maybe? – Curt Mar 27 '14 at 16:41
@Curt - The COUNT of this query would be the inverse of your temp table count. This query fetches all records that are NOT the max, your temp table would contain only the max records. And if you have any duplicate max rows, it will only include one row for those records. – PinnyM Mar 27 '14 at 16:48
Ah yes...thank you. The greater then symbol through my mind in the wrong direction. But the update wont happen. Error: "cant specify target table for update in FROM clause. So I ended up changing the WHERE EXISTS table you had to be the temp table that I created. Also changed in WHERE clause to be AND maxUser.endDate = u.endDate. That gives me the records to be updated then. Is that a good way to do it? – Curt Mar 27 '14 at 17:23
@Curt - this error is because my syntax wasn't MySQL supported. Updated with a working syntax and fiddle to show the results. Your approach can certainly work, but is slower and more error prone as it involves more working parts that aren't necessary. – PinnyM Mar 27 '14 at 18:08
I tried your updated version with JOIN, but doesnt get me the correct results. I think its close and could get it to work, but found something that is fast and works. Still marking this as the winner since it led me to this: [link](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) The error I provided led me to another SO question and the answer provided by @PratikKhadloya was the one that worked for me. Extremely fast too. Thanks for the suggestions all. – Curt Mar 27 '14 at 18:57
1

@Curt - reading your problem again, I realized I was solving the inverse problem. Updated with SQL (and fiddle) that works and avoids the recursive subquery. – PinnyM Mar 27 '14 at 19:13
Yes! works perfect! and is 1 second faster then other solution, which makes sense because its not doing the subquery as you stated! Im not dealing with a lot of records on the test server so doing it this way will save time! Thanks again. – Curt Mar 27 '14 at 19:20

score 0 · Answer 2 · answered Mar 27 '14 at 16:23

0

A faster way would be to not use functions. Try something like this:

update user
set enddate = null
from user u join keyvaluepairs k on u.user = k.user
and u.enddate = k.menddate

answered Mar 27 '14 at 16:23

Dan Bracuk

20,699
4
26
43

The from clause wont work there, I think you meant to put a WHERE clause maybe? – Curt Mar 27 '14 at 16:30
I don't use mysql but I assumed that it would support this construct. Maybe this was one of those occasions where assume has three syllables. – Dan Bracuk Mar 27 '14 at 17:11

Update records with a key pair

2 Answers2