Member Avatar for Kraso

I have a table where I want to update "time 2" row, in the user's last "time 1" entry. I was try this, but not function.

UPDATE `temp` SET `time2`=NOW() WHERE user = 'kraso' (SELECT max(time1) FROM 'temp')
Edited by Dani because: Formatting fixed
  • 6 Contributors
  • 5 Replies
  • 740 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by Sherin Mathew

Dani AI

Generated

Short expert note tying the thread together and practical options.

The subquery answers from and are valid for finding the row with the greatest time1, but both forms that compare to MAX(time1) will match every row that has that same max value (so duplicates can be updated). A succinct MySQL single-statement that updates only one row (the latest for that user) uses ORDER BY with LIMIT 1:

UPDATE temp
SET time2 = NOW()
WHERE user = 'kraso'
ORDER BY time1 DESC
LIMIT 1;

This is a supported single-table UPDATE pattern (rows are updated in the ORDER BY sequence and LIMIT restricts how many matches are affected). (dev.mysql.com)

For higher concurrency safety (avoid race conditions when multiple sessions may insert/update simultaneously), grab the target row inside a transaction with a locking read, then update by primary key:

START TRANSACTION;
SELECT id FROM temp
  WHERE user = 'kraso'
  ORDER BY time1 DESC
  LIMIT 1
  FOR UPDATE;

UPDATE temp
  SET time2 = NOW()
  WHERE id = <id>;
COMMIT;

SELECT ... FOR UPDATE locks the chosen row(s) until commit, preventing other transactions from modifying them. (dev.mysql.com)

For performance, make sure the optimizer can find the latest row fast by indexing the filtering and ordering columns (for example a composite index on (user, time1)). Adding such an index speeds the ORDER BY + LIMIT pattern. (dev.mysql.com)

A quick precaution: run a SELECT first to confirm which row will be affected (for example, SELECT * FROM temp WHERE user='kraso' ORDER BY time1 DESC LIMIT 1) and consider whether the business rule expects one latest row or all rows that share the same max timestamp before choosing the single-update vs. multi-row approach.

Recommended Answers

All 5 Replies

Member Avatar for rproffitt

That's an odd choice for your forum post. A code snippet is for you to share your final work. If you have a question, use the other styles.

Member Avatar for Halla_1

I need a code snippet or at least a screenshot.

Member Avatar for chuckc

Sometimes it helps to go back to the basics: Consider

Update <table name> set <column name> = <value> where <row name> = <row value>;

Member Avatar for dr.vivek

UPDATE temp SET time2=NOW() WHERE user = 'kraso' AND time1=(SELECT max(time1) FROM temp WHERE user='kraso')

Member Avatar for Sherin Mathew

By this Query , required data is fetched!!

UPDATE temp SET time2=NOW() WHERE  time1 in (SELECT max(time1) FROM temp WHERE user='kraso')
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.