Conditional update between two tables in SQL-CodePudding

In the two tables below, for students and parents with the same school names: I want it to assign parent_name to student_name.

CREATE TABLE students2 (
     student_id CHAR(2),
     student_name VARCHAR2(30),
     student_grade NUMBER(2),
     school_name VARCHAR2(50)
)



CREATE TABLE parents (
     student_id CHAR(2),
     parent_name VARCHAR2(30),
     school_name VARCHAR2(50)
)


UPDATE students2
SET student_name = (SELECT parent_name 
                    FROM parents 
                    WHERE parents.school_name = students2.school_name)

When I write this query, the update is done BUT the student_names that do not meet the condition are made NULL. They were not supposed to change.

CodePudding user response：

You need to say which rows should be updated; see WHERE EXISTS.

UPDATE students2 a SET 
  a.student_name = (SELECT b.parent_name 
                    FROM parents b
                    WHERE b.school_name = a.school_name)
WHERE EXISTS (SELECT NULL
              FROM parents c
              WHERE c.school_name = a.school_name);

Alternatively, as you didn't learn EXISTS yet:

UPDATE students2 a SET 
  a.student_name = (SELECT b.parent_name 
                    FROM parents b
                    WHERE b.school_name = a.school_name)
WHERE a.school_name IN (select c.school_name
                        FROM parents c);

CodePudding user response：

According to what I understood, if you want to assign parent_name to student_name, shouldn't the query look like-

UPDATE students2 SET student_name = (SELECT parent_name FROM parents WHERE parents.student_id= students2.student_id)

Anyways, if I'm missing something or you already know about that, maybe you can try this for null values:

UPDATE students2 SET student_name =(SELECT parent_name FROM parents
WHERE
    CASE
        WHEN %s IS NOT NULL
            < statements > 
        ELSE < statements >
        END
    [AND/OR] <other constraints here> )

Please correct me if I'm wrong