英文:
Replace All Rows in firsttable with Rows from secondtable with Corresponding IDs
问题
只返回翻译好的部分,不包括代码:
id speed
1 12.3
1 12.4
2 12.43
2 11
2 13.6
英文:
I have these two tables:
create table firsttable (id int, speed double precision);
create table secondtable (id int, speed double precision);
insert into firsttable (id)
values (1), (2);
insert into secondtable (id, speed)
values (1, 12.3), (1, 12.4), (2, 12.43), (2, 11), (2, 13.6), (3,23);
I want to update firsttable
's speed column with values from secondtable
required result:
id speed
1 12.3
1 12.4
2 12.43
2 11
2 13.6
insert into firsttable(speed)
select speed from secondtable
where id in firsttable.id
ERROR: syntax error at or near "firsttable"
LINE 4: where id in firsttable.id
EDIT
The answer by Tushar gives:
id speed
1 null
2 null
1 12.3
1 12.4
2 12.43
2 11
2 13.6
EDIT-2
I tried:
INSERT INTO firsttable (id, speed)
SELECT id,speed
FROM secondtable
WHERE EXISTS (SELECT id FROM firsttable);
id speed
1 null
2 null
1 12.3
1 12.4
2 12.43
2 11
2 13.6
3 23
But then the firt 2 rows and the last row should not be in the answer (see required answer).
答案1
得分: 1
根据所需行为的描述,标题似乎应该是“用与对应ID的Table2中的行替换Table1中的所有行”。以下查询将实现这一目标:
WITH ids AS
(DELETE FROM firsttable
WHERE id IN (SELECT id FROM secondtable)
RETURNING id)
INSERT
INTO firsttable (id, speed)
SELECT id, speed
FROM secondtable
WHERE id IN (SELECT id FROM ids)
RETURNING *;
英文:
From the description of the desired behavior, it appears that the title should have been Replace All Rows in Table1 with Rows from Table2 with Corresponding IDs. The following query will do this:
WITH ids AS
(DELETE FROM firsttable
WHERE id IN (SELECT id FROM secondtable)
RETURNING id)
INSERT
INTO firsttable (id, speed)
SELECT id, speed
FROM secondtable
WHERE id IN (SELECT id FROM ids)
RETURNING *;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论