2

When I perform an UPDATE query I would like to be able to know if the operation:

  • was successfully performed and has changed the value
  • was successfully performed, but the value was the same of the one in DB
  • was not successfully performed

So, suppose I have only one row in DB and it has columns id=1 and my_value="abc" and I perform the following queries:

1) UPDATE goals SET my_value = 'xyz' WHERE id = '9' (it would fail on where)
2) UPDATE goals SET my_value = 'xyz' WHERE id = '1' (it will go fine)
3) UPDATE goals SET my_value = 'abc' WHERE id = '1' (it is exactly the same as the already stored column)

Using $stmt->affected_rows I can distinguish the query 2 from the others ($stmt->affected_rows value would be 1), but I cannot distinguish between queries 1 and 3 (both $stmt->affected_rows values will be 0).

Is there a way to know that without to perform a SELECT query with the WHERE id = '<my-id>' statement?

George Redivo
  • 87
  • 1
  • 7
  • 1
    One approach `UPDATE goals SET my_value = 'abc' WHERE id = '1' AND my_value != 'abc'`, or use a transaction, do a select to check value prior to update. Also maybe look at `UPDATE IGNORE`. – ficuscr Jan 18 '19 at 18:29
  • You could run a select first to check case 1, then run the update and as you know the row exists, `affected_rows` will return 1 for case 2 and 0 for case 3. – Nigel Ren Jan 18 '19 at 18:57

1 Answers1

0

Here's a trick:

SET @i = 0;
UPDATE goals SET my_value = 'xyz', id = id+(@i:=@i+1)-@i WHERE id = '1';

After this runs, @i will be incremented by the number of matching rows, whether the UPDATE changed the value or not.

The trick is to both add and subtract the value of @i, which will be a net change of 0.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828