CODEWITHSUNDEEP
postgresql
Eric G
Eric G

Reputation: 926

PostgresSQL Copy a row to the same table and alter one value

I want to insert a new row which copies the two fields from the original row, and changes the last field to a new value. This is all done on one table.

Please excuse the table names/fields, they are very long.

Table 1 - alert_template_allocations

Table 2 - io

My Attempt

insert into alert_template_allocations
(alert_template_allocation_io_id,
alert_template_allocation_alert_template_id,
alert_template_allocation_user_group_id)
values 
(
    (Select at.alert_template_allocation_io_id,
    at.alert_template_allocation_alert_template_id
    from alert_template_allocations at join io i on
    i.io_id = at.alert_template_allocation_io_id
    and i.io_station_id = 222)
, 4);

Upvotes: 3

Views: 2261

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175766

Use INSERT INTO SELECT syntax:

enter image description here

INSERT INTO alert_template_allocations (alert_template_allocation_io_id,
                                        alert_template_allocation_alert_template_id,
                                        alert_template_allocation_user_group_id)
SELECT at.alert_template_allocation_io_id,
       at.alert_template_allocation_alert_template_id,
       4
FROM alert_template_allocations at 
JOIN io i 
  ON i.io_id = at.alert_template_allocation_io_id
 AND i.io_station_id = 222;

Upvotes: 3

Related Questions