stack stack - 7 months ago 10
SQL Question

How can I check the value of record before updating?

I have this table:

// QandA
+----+-----------+-----------------+-------------+------+------+
| id | post | accepted-answer | question_id | let | type |
+----+-----------+-----------------+-------------+------+------+
| 1 | question1 | null | 1 | 200 | 0 |
| 2 | answer1 | null | 1 | null | 1 |
| 3 | answer2 | 1 | 1 | null | 1 |
| 4 | answer3 | null | 1 | null | 1 |
+----+-----------+-----------------+-------------+------+------+
// ^ 0 = question | 1 = answer


Also I have this query:

// this query changes accepted answer
UPDATE QandA
SET accepted_answer = IF(id <> :id, NULL, 1)
WHERE question_id = :question_id;





Now I'm trying to check
let
field before updating. Something like this:

if (`let` is null) then {update here}
else {don't update}
where `question_id` = :question_id and `type` = 0


How can I do that in MySQL?

Answer

You can use the case statement , the below statement will update the QandA table .

It will set accepted_answer to be 1 when let is null and the type is 0.

Give it a try , I know it works on ms sql , just cant access my sql box at the moment.

UPDATE QandA SET accepted_answer =  
CASE
  WHEN let = IS NULL THEN 1
END
WHERE type= 0;
Comments