Insert rows into table for multiple ids if the key doesn't exit

Question

I have a table called Configuration. The 1st column of the table is deviceId, the 2nd column is parameter, 3rd column is value.

In the table, there are many devices, each device has only one device ID(column 1); each device has many config parameters(column 2), ex. VER, DATE, etc, all devices have the same config parameters; the parameter's value(column 3) for different devices may be the same or not.

I want to insert a new config parameter say TITLE with value newDevice to the devices which has config parameter DATE value larger than '2019-05-01'.

How can I achieve this in one PostgreSQL query?

Mark · Accepted Answer · 2019-07-08 05:32:11Z

1

You can use an insert into with select statement. First, try the select to see if you can generate query results that look like the rows you want to create:

select deviceid, 'TITLE', 'newDevice' 
from Configuration 
where parameter = 'DATE' 
  and value > '2019-05-01'

If the output from the above query looks good, try combining it with an insert like this:

insert into Configuration (deviceid, parameter, value) 
select deviceid, 'TITLE', 'newDevice' 
from Configuration 
where parameter = 'DATE' 
  and value > '2019-05-01'

edited Jul 8, 2019 at 5:32

user330315

answered Jul 8, 2019 at 0:55

Mark

9897 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert rows into table for multiple ids if the key doesn't exit

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related