Updating field only if certain value is found in other table

Question

I am stuck on the following issue: I have two tables in my database. One holds parent elements and one holds child elements. This structure is given and must not be changed. I now have to update a field for each child. The field may contain 0 or 1 but must not be NULL. The default value is 0. If the parent holds a certain status, i want to update the field in the child category to 1. I have tried this so far:

UPDATE  'children' AS 'child'
    SET  'foo' = (
        SELECT 1
        FROM 'parents' AS 'parent'
        WHERE 'parent'.'type' = "bar" AND 'parent'.'id' = 'child'.'fk_parents_id'
    );

This doesn't seem to work for me as I get an error message stating foo must not be null. I am fairly new to SQL, so I am quite stuck here. Can someone help me out?

something like `foo = (SELECT CASE WHEN 'parent'.'type' = "bar" THEN 1 ELSE 0 END FROM ...)` ? — Cid, Sep 07 '20 at 12:22

score 1 · Answer 1 · answered Sep 07 '20 at 12:22

I'm assuming here that the field children.foo is supposed to get the value 0 if the subquery returns NULL as no parent with the right conditions was found. In that case the original query fails because for rows with no matching parent the SET = (...) part tries to assign NULL to a field that is not nullable.

If the two values directly match to the existence of a matching parent, the query can then be changed to:

UPDATE  'children' AS 'child'
    SET  'foo' = IFNULL((
        SELECT 1
        FROM 'parents' AS 'parent'
        WHERE 'parent'.'type' = "bar" AND 'parent'.'id' = 'child'.'fk_parents_id'
    ), 0);

score 1 · Accepted Answer · answered Sep 07 '20 at 12:26

if foo can't be NULL. so you have to test if it gets an result and set another value

UPDATE  `children` AS `child`
    SET  `foo` = IF(
        EXISTS(SELECT 1
        FROM `parents` AS `parent`
        WHERE `parent`.`type` = "bar" AND `parent`.`id` = `child`.`fk_parents_id`) 
    ,0, 1);

Also never never use single qutotes for table or coumn names that gives only errors see When to use single quotes, double quotes, and backticks in MySQL

score 0 · Answer 3 · answered Sep 07 '20 at 12:21

0

     UPDATE  'children' AS 'child'
     SET  'foo' = (
        SELECT 1
        FROM 'parents' AS 'parent'
        WHERE 'parent'.'type' = "bar" AND 'parent'.'id' = 
        'child'.'fk_parents_id'
     ) 
     Where foo >= 0

answered Sep 07 '20 at 12:21

Ashish Mishra

412
4
5

Updating field only if certain value is found in other table

3 Answers3