stack stack - 6 months ago 11
MySQL Question

How can I understand which condition fails?

I have a query like this:

UPDATE table1 t1
JOIN table2 t2
ON t1.col1 = t2.col2
SET t1.col3 = "something"
WHERE t1.col4 = t2.col3 AND
t1.col5 IS NOT NULL


Sometimes one of those conditions on the
WHERE
clause is
FALSE
and then nothing updates. I'm trying to understand why nothing updates? I mean I want to set an error for each condition to specify me which condition is
FALSE
.

I'm not sure but maybe using
@variable
or using
CASE WHEN
would be a approach. Is there any solution?




My query in reality:

UPDATE
qanda AS ans1
JOIN qanda AS ans2 ON ans2.related = ans1.related
JOIN qanda AS ques ON ans2.related = ques.id
SET ans1.acceptedanswer = IF( ans1.id <> ?, 0, IFNULL( ans1.acceptedanswer, 0 ) ^ b'1' ),
ans1.aadate = IF( ans1.id <> ?, ans1.aadate, ?)
WHERE ques.author_id = ?
AND ans2.id = ?
AND ans2.author_id = ?
AND (ques.amount IS NULL or ans1.acceptedanswer IS NULL

Answer

You could try SELECT and CASE to print failed condition as bellow:

SELECT
CASE 
   WHEN (t1.col4 = t2.col3) = FALSE THEN 'FAILED condition: t1.col4 = t2.col3'
   WHEN (t1.col5 IS NOT NULL) = FALSE THEN 'FAILED condition: t1.col5 IS NOT NULL'
END
 FROM table1 t1
  JOIN table2 t2
    ON t1.col1 = t2.col2
SET t1.col3 = "something"
WHERE (t1.col4 = t2.col3 AND
      t1.col5 IS NOT NULL) = FALSE

EDIT: UPDATE returns the number of rows that were actually changed See MySql ref. So you need to run it in different call.

N.B. I didn't run it; just tried to share a concept.

Comments