Conditional update between two tables in SQL

Question

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.

Answer 1

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