stack stack - 1 year ago 118
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
clause is
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

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

My query in reality:

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

Answer Source

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

   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'
 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download