CODEWITHSUNDEEP
mysqlmariadb
Konzertmeister
Konzertmeister

Reputation: 41

Updating field only if certain value is found in other table

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?

Upvotes: 1

Views: 41

Answers (3)

nbk
nbk

Reputation: 49375

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

Upvotes: 1

Etienne Ott
Etienne Ott

Reputation: 676

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);

Upvotes: 1

Ashish Mishra
Ashish Mishra

Reputation: 422

     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

Upvotes: 0

Related Questions