Why is my UPDATE query updating all rows?

Question

I am trying to update the row most recently added to my table by adding a WHERE clause and selecting the MAX 'dressingID' which is my primary key.

The function is working however it is returning every row instead of just the row with the largest ID.

Here is the query:

UPDATE Dressing set dressingCode='111' WHERE dressingID=(SELECT MAX(dressingID))

Table Name = Dressing

Primary Key = dressingID

I am trying to get a number from a QR code and pass to to the latest entry into the table, however it is updating every row for some reason, any help would be appreciated. Cheers!

Unfortunately this query does not affect any rows – phpsandqs Jul 10 '19 at 15:35 — phpsandqs, Jul 10 '19 at 15:35

Lukasz Szozda · Accepted Answer · 2019-07-10T15:34:59.623

2

You need to reference table:

UPDATE Dressing set dressingCode='111' 
WHERE dressingID=(SELECT d FROM ((SELECT MAX(dressingID) AS d FROM Dressing)) s);
                  -- forced materialization

db<>fiddle demo

MariaDB 10.3:

UPDATE Dressing set dressingCode='111' 
WHERE dressingID=(SELECT MAX(dressingID) AS d FROM Dressing);

db<>fiddle demo

Related: You can't specify target table for update in FROM clause

Original query has A=MAX(A) condition for single row:

UPDATE Dressing set dressingCode='111' 
WHERE dressingID=(SELECT MAX(Dressing.dressingID))
<=>
UPDATE Dressing set dressingCode='111' 
WHERE dressingID=Dressing.dressingID
-- always true for non-nullable dressingID

edited Jul 10 '19 at 15:34

answered Jul 10 '19 at 15:29

Lukasz Szozda

162,964
23
234
275

This query returns the error: `#1093 - Table 'Dressing' is specified twice, both as a target for 'UPDATE' and as a separate source for data`\ – phpsandqs Jul 10 '19 at 15:33
@phpsandqs Please check a demo. – Lukasz Szozda Jul 10 '19 at 15:33

Why is my UPDATE query updating all rows?

1 Answers1