UPDATE table - 0 rows affected-CodePudding

I wrote an UPDATE command whose syntax is good, but it doesn't affect any of the rows, so it doesn't actually work. [1] [1]: https://i.stack.imgur.com/MXinf.png

update T1 c, T2 dk
    set c.date = dk.dates, c.field = 'event_dt'  
where c.id = dk.id and c.age = dk.age and c.height = dk.height and c.country = dk.country
    and c.id is not null and c.date is null and c.age is not null and c.height is not null and c.country is not null;

how can I solve the problem? thanks!

CodePudding user response：

I would suggest writing this as an update join:

UPDATE T1 c
INNER JOIN T2 dk
    ON c.id = dk.id and c.age = dk.age and c.height = dk.height and c.country = dk.country
SET c.date = dk.dates, c.field = 'event_dt'  
WHERE c.id IS NOT NULL AND c.date IS NULL AND c.age IS NOT NULL AND
      c.height IS NOT NULL AND c.country IS NOT NULL;

If the above also does not update any rows, then either your data or your logic is off.