sql - 如何将数据从一个表插入另一个表并用所需数据填充其他列

Question

我设计了这四张桌子：

CREATE TABLE Person(
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(50) 
);


CREATE TABLE Coach(
    id VARCHAR(50) PRIMARY KEY,
    team VARCHAR(50) , 
    FOREIGN KEY (id) REFERENCES Person(id)
);

CREATE TABLE Player(
    id VARCHAR(50) PRIMARY KEY,
    team VARCHAR(50) , 
    age INT , 
    FOREIGN KEY (id) REFERENCES Person(id)
);

CREATE TABLE Refree(
    id VARCHAR(50) PRIMARY KEY,
    fifa_grade VARCHAR(50) , 
    FOREIGN KEY (id) REFERENCES Person(id)
);

CREATE TABLE Team(
    name varchar(30) PRIMARY KEY
);

我想将所有不在 refree 、coach 和 player 表中的人插入到player表中，并将球员团队设置为例如 'barcelona' 并将球员年龄设置为 30 岁，我尝试过代码打击，但它给了我语法错误：

insert into player (id,'chelsea',24)
select id
from person
where person.id not in (select id from coach) and person.id not in (select id from player) and person.id not in (select id from refree)

我该如何修复此代码？

标签： sqlpostgresql

Answer 1

您的查询不正确，您可以使用以下值select：

INSERT INTO player (id, team, age)
SELECT id, 'barcelona', 30
from person p
left join coach c on p.id = c.id
left join player ply on p.id = ply.id
left join refree r on p.id = r.id
WHERE c.id IS NULL and ply.id IS NULL and r.id IS NULL