How can I update a row only if a select query returns one row?

Question

I want to check if two user supplied values match a row in the database, and if they do, change an enum value from 0 to 1.

I know I can do this in two queries, but I'd rather do it in one. The only thing I've been ale to find is how to update a row if it exists, and create it if it doesn't. However, I don't want this, as if the supplied values are incorrect the script should exit.

Can I use IF EXISTS (SELECT x FROM x where y = y AND z = z) UPDATE ...

Will this work? And if so, how can I check if it executed within PHP?

i think your over complicating this see below – Jan 18 '16 at 03:07 — , Jan 18 '16 at 03:07

score 3 · Answer 1 · answered Jan 18 '16 at 03:06

3

I think you would just use where with update:

update x
    set enumcol = 1
    where y = $y and z = $z;

This updates rows where values match. If no rows match, then no rows are updated.

answered Jan 18 '16 at 03:06

Gordon Linoff

1,242,037
58
646
786

hmm, I should have thought of this. How can I know if it was executed correctly however? – cantsay Jan 18 '16 at 03:07
3

@cantsay mysqli_affected_rows – Jan 18 '16 at 03:09

score 2 · Answer 2 · edited May 23 '17 at 11:44

Wouldnt that just be done with a standard condition? Like:

UPDATE my_table 
SET enum_field=1 
WHERE one_field=[first_value] AND another_field=[second_value]

Maybe you also would have to match against a user ID or something:

UPDATE my_table 
SET enum_field=1 
WHERE one_field=[first_value] AND another_field=[second_value] AND id=[user_id]

In more complex cases this answer might help:

MySQL: update a field only if condition is met

How can I update a row only if a select query returns one row?

2 Answers2