Question 237194 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Update Most Recent Date

I have a table that stores member info.
The column ‘added’ stores the date they joined.

View Table

I want to get the most recent dates in the added column and update them to a specific date. Sometimes it is a single record, sometimes multiple.

The problem is using UPDATE and SELECT for the same table? Commonly this seems to be done more across two tables. I only have one.

This is what I’ve tried:

UPDATE `test`
SET `added` = `2023-08-24`
WHERE `added` =
(
SELECT * FROM `test` WHERE `added` = (select max(`added`) FROM `test`);
)

The SELECT command works when ran on the table so I know the data is being retrieved correctly. It’s the combination of SELECT and UPDATE that I don’t think works?

I will be executing the code from a PHP file.

UPDATE: Stack Overflow won’t let me answer my own question 🙄

The solution seemed to be adding a subquery.
Found from Infobird blog.

UPDATE `test` SET `added` = '2023-08-24'
WHERE `added` IN (
  SELECT * from (
    SELECT MAX(`added`) FROM `test`
  ) as t
);

Seems to work correctly for me.

Tags:

2

Answers


  1. 0

    You can use a subquery to select the maximum date and then updating the rows with that date.

    UPDATE `test`
SET `added` = '2023-08-24'
WHERE `added` = (SELECT MAX(`added`) FROM `test`);

    However, this query will update all rows with the most recent date to the specified date. If you only want to update a specific number of rows, you could use this query instead:

    UPDATE `test`
SET `added` = '2023-08-24'
WHERE `added` IN (SELECT `added` FROM `test` ORDER BY `added` DESC LIMIT 5);

    Change the ‘5‘ to a integer of your preference.

    Since you mentioned that you will be executing this code from a PHP file, make sure to use proper error handling and SQL injection prevention techniques in your PHP code.

    Login or Signup to reply.
  2. 0

    This is a way to do it using inner join :

    update test t
inner join (
  select MAX(added) as max_added 
  FROM test
) as s on s.max_added = t.added
set t.added = '2023-08-24'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search