Update Rows Based On Another Table

Create Table Of Elves

-- Create table called elves
CREATE TABLE elves (
    -- string variable
    name varchar(255),
    -- integer variable
    age int,
    -- string variable
    race varchar(255),
    -- string variable
    alive varchar(255)
)

Create Table Of Deaths

-- Create table called deaths
CREATE TABLE deaths (
    -- string variable
    name varchar(255)
)

Insert Rows Into Elf Table

INSERT INTO elves (name, age, race, alive)
VALUES ('Dallar Woodfoot', 25, 'Elf', 'Yes'),
       ('Cordin Garner', 29, 'Elf', 'Yes'),
       ('Keat Knigh', 24, 'Elf', 'Yes'),
       ('Colbat Nalor', 124, 'Elf', 'Yes')

Insert Rows Into Deaths Table

INSERT INTO deaths (name)
VALUES ('Keat Knigh'),
       ('Colbat Nalor')

View Elves Table

-- Retrieve all rows from the view Elf
SELECT * FROM elves
nameageracealive
Dallar Woodfoot25ElfYes
Cordin Garner29ElfYes
Keat Knigh24ElfYes
Colbat Nalor124ElfYes

Update Rows Based On Another Table

-- Change the value in elves
UPDATE elves
-- to set alive to "No"
SET alive = 'No'
-- Where the name of the elf is in the list of deaths
WHERE elves.name in (SELECT deaths.name FROM deaths)

View Elves Table

-- Retrieve all rows from the view Elf
SELECT * FROM elves
nameageracealive
Dallar Woodfoot25ElfYes
Cordin Garner29ElfYes
Keat Knigh24ElfNo
Colbat Nalor124ElfNo