How to use if/else in an UPDATE statement

Question

Here is my current query

UPDATE `records` SET tester1 = '$user', tester1yn = '$pass'

What I would like to change this to is

UPDATE `records` SET
IF `tester1` IS NULL `tester1` = $user, AND `test1` = $pass
ELSE `tester2` = $user, `tester1yn` = '$pass'
WHERE `id` = $id

But as I have it this does not work. Am I having a nested issue, or I am going about this the wrong way?

UPDATE BASED ON FEEDBACK

UDPATE records SET 
tester1 = if(tester1 IS NULL,'$user',tester1), 
tester1yn = if(tester1 is null, '$pass', tester1yn), 
tester2 = IF(tester1 is not null, '$user', tester2), 
tester2yn = IF(tester1 is not null,'$pass', tester2yn) 
where id = $id

still not there yet though.

Programming by _guessing_ does not work. Read the MySQL documentation to find out what its features are. Note that queries are single declarative statements, not procedural functions. — Lightness Races in Orbit, May 01 '13 at 20:00
possible duplicate of [MYSQL IF ELSE statement in query?](http://stackoverflow.com/questions/8763310/mysql-if-else-statement-in-query) — Rene Pot, May 01 '13 at 20:00
This is a classic example of MySQL Injection, please see: http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php — Pier-Luc Gendreau, May 01 '13 at 20:01
See [`CASE` (expression)](http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case) in the MySQL documentation (hint: use the result of the expression). Then read up on [avoiding SQL injection](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php). — user2246674, May 01 '13 at 20:01
I don't have any fear about MySQL injection. The whole project is a really dirty hack to begin with. — Dan Ciborowski - MSFT, May 01 '13 at 20:09

score 2 · Accepted Answer · edited May 01 '13 at 20:26

2

You're kind of heading in the wrong direction, though this logic should be in the next layout out (in my opinion)

Try:

UPDATE records 
SET
tester1 = if(tester1 IS NULL,$user,tester1),
tester1yn = IF(tester1 IS NULL,$pass,tester1yn),
tester2 = IF(tester1 IS NOT NULL, $user,tester2),
tester2yn = IF(tester1 IS NOT NULL,'$pass',tester2yn)
WHERE id = $id";

edited May 01 '13 at 20:26

Dan Ciborowski - MSFT

6,807
10
53
88

answered May 01 '13 at 20:06

Chris

2,955
1
30
43

Updated to this put still not working. i add "'" around all my php vars starting wtih $ – Dan Ciborowski - MSFT May 01 '13 at 20:21
had to switch the order of the tests to make it work correct. – Dan Ciborowski - MSFT May 02 '13 at 15:17

score 2 · Answer 2 · answered May 01 '13 at 21:40

I didn't test this, so don't hold me to it, but this should point you in the right direction:

UPDATE records 
SET 
    tester1 = CASE WHEN tester1 IS NULL THEN '$user' ELSE tester1 END,
    test1 = CASE WHEN tester1 IS NULL THEN '$pass' ELSE test1 END,
    tester2 = CASE WHEN tester1 IS NOT NULL THEN '$user' ELSE tester2 END,
    tester2yn = CASE WHEN tester1 IS NOT NULL THEN '$pass' ELSE tester2yn END
WHERE
    id = $id

score 0 · Answer 3 · answered May 01 '13 at 20:06

0

While it is doable in SQL, that sort of logic shouldn't be happening at the database level. The login, authentication and management of users (which seems to be what you're after) should be done in your code.

answered May 01 '13 at 20:06

Pier-Luc Gendreau

13,553
4
58
69

How to use if/else in an UPDATE statement

3 Answers3