using IF statement in mysql update query

Question

I am trying to find a way to insert a value into one of two columns depending on which one is NULL.

$accept_sth = $dbh->prepare("UPDATE user_properties 
    IF(followup_offer <> NULL, fstatus=?)
    ELSE (istatus=?) 
    WHERE id=?");

$accept_sth->execute($_POST['option'], $_POST['id']);

I am doing it wrong.

Put your conditional logic in the php, not in the sql statement. Modify your SQL statement based on the condition of followup_offer in the php. — random_user_name
– random_user_name, Commented Sep 20, 2013 at 23:25
but then I would have to query the database to find out if followup_offer is NULL right. I was wanting to do it in one query. — Smith Smithy
– Smith Smithy, Commented Sep 20, 2013 at 23:27
Get followup_offer in one of your existing / previous queries. You have status and id, so get followup_offer at that same time. — random_user_name
– random_user_name, Commented Sep 20, 2013 at 23:30
It may be possible, but it will require some unnecessary brain damage to get it right, and it's going to make for a messy sql statement. Simple is better, if possible. — random_user_name
– random_user_name, Commented Sep 20, 2013 at 23:37

peterm · Accepted Answer · 2013-09-22 04:05:15Z

3

No ...brain damage... necessary. You can do it with a query

UPDATE user_properties 
   SET fstatus = IF(followup_offer IS NULL, fstatus, ?),
       istatus = IF(followup_offer IS NULL, ?, istatus)
 WHERE id = ?

Note: the only possible drawback for some scenarios of this type of query with conditional SET is that both columns are updated every time (one with a value and one with the old one).

Here is SQLFiddle demo

edited Sep 22, 2013 at 4:05

answered Sep 21, 2013 at 4:04

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Smith Smithy Over a year ago

Although I went with another solution that works (omitting the IF all together) this is the answer to the question I had. I don't see a big issue with both columns being updated. Thank you!

Andrew Ensley Over a year ago

Brain damage, good grief. The comment suggesting splitting data-logic between PHP and MySQL is a terrible recommendation. This is an excellent answer.

Collectives™ on Stack Overflow

using IF statement in mysql update query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related