13

I need to do something that seems to be very simple:

$bdd->query('UPDATE mytable SET aaa = \''.$aaa.'\', bbb = \''.$bbb.'\' WHERE name = \''.$name.'\'');

My problem: I have multiple records that match this "WHERE", and I want to update only the most recent one (I have a date and an id that can be used to define which record is the most recent)

How can I change my WHERE to add something like "AND id = the_highest_id_of_this_query"?

jrm
  • 885
  • 5
  • 12
  • 20
  • Nothing to do with your question, which is answered below, but I might suggest using double quotes around your query so you don't have to escape all the single quotes. You make queries easier to read and less error prone to write that way. – Mike Brant Jan 24 '13 at 16:20
  • 1
    Please, just be aware that you're potentially open to SQL Injection. It's always best to use parameterized queries, even when the query doesn't use any external data, just to stay consistent, and follow good practices. – Clockwork-Muse Jan 24 '13 at 17:08

2 Answers2

33

You can limit to update only the most recent record

UPDATE your_table
SET some_column = 1
order by date_time_column desc
limit 1

where date_time_column can be any column indicating the order of the records. It could be an auto-increment ID too.

juergen d
  • 201,996
  • 37
  • 293
  • 362
4
UPDATE table
SET column = value
WHERE primary_key =
(
SELECT primary_key
FROM table 
WHERE date_time_column = (select max(date_time_column) FROM table WHERE other_conditions)
)
AND other_conditions

This query does not use order by or limit clause and therefore will be portable. Note that other_conditions have to be same in the inner query and outer query.

(Since this was posted in a comment) Why does the inner query need to have the same condition as the outer one?

  • If the inner condition fetches a broader resultset than the outer one, you could end up with a date_time_column that is earlier than those contained in the rows satisfying the outer condition. ANDing them will then result in a fetch of 0 rows.

  • If the inner condition fetches a narrower result set than the outer one, you could end up missing out on any of the records that are newer, not part of the inner set yet satisfied as part of the outer condition.

Hope this clarifies.

alok
  • 1,218
  • 1
  • 12
  • 29
  • ...`other_conditions` likely do _not_ have to be the same in the inner and outer query, if his primary key is unique (which is usually the case). Also, if the maximum date/time is being used to find the primary key, which is used to find the record to update (ie, there's only 1 row with a particular timestamp), you can just shortcut using the primary key, and use the date/time column. – Clockwork-Muse Jan 24 '13 at 17:07
  • other_conditions *does* have to be the same in both the queries. The explanation is long so editing my answer to explain – alok Jan 28 '13 at 11:02
  • I read alok's reasons that other_conditions need to be in both inner and outer query several times, and I still don't understand why it's necessary. The inner query should return a SINGLE ID, and it should be a unique key. The only thing the outer query needs to know is that unique key that identifies the SINGLE ROW it needs to update. The outer query doesn't need the other_conditions. Unless I'm missing something... – Josh Jun 29 '13 at 19:26
  • 1
    Also, I tried this method and got the following MySQL error: "You can't specify target table 'customers' for update in FROM clause" – Josh Jun 29 '13 at 19:27
  • And the solution for the "can't specify target in from clause" issue is to use a nested subquery, which (apparently) creates an implicit temporary table. http://stackoverflow.com/a/4429409/450668. Or, use juergen d's solution, above. – Josh Jun 29 '13 at 19:37
  • The same table cannot be in UPDATE and sub SELECT query. – sbrbot Aug 22 '14 at 22:19