用第二个表中具有相应ID的行替换第一个表中的所有行。

huangapple go评论71阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年3月4日 01:24:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/75630132.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定