How to update some columns of multi rows in PostgreSQL

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, COMPANY 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 update two config parameters say VER and DATE with new parameter's values(column 3) for the device which has ID equal to "id12345".

How can I achieve this in one PostgreSQL query?

Deepstop · Accepted Answer · 2019-07-16 02:08:23Z

1

This is what you could do, although I don't think it's a terrific idea.

UPDATE configuration
    SET value = 
        CASE parameter 
            WHEN 'VER' THEN new_ver_value
            WHEN 'DATE' THEN new_date_value
        END
    WHERE deviceid = 'id12345';

Parameter tables like this are generally considered a bad idea, as the complexity of this query helps illustrate. Also, since you say that all the devices have the same parameters, doing this instead of having a unique column for each parameter doesn't seem to achieve anything useful.

Also if possible use just a number for the deviceid instead of a string.

As requested, to update an additional field, like a time field set to the current_time, you could do the following.

UPDATE configuration
    SET value = 
        CASE parameter 
            WHEN 'VER' THEN new_ver_value
            WHEN 'DATE' THEN new_date_value
        END,
        time = current_time
    WHERE deviceid = 'id12345'
        AND parameter IN ('VER', 'DATE');

For the first query, here is a test to show the result.

CREATE TABLE configuration 
   (deviceid CHARACTER VARYING (20),
    parameter CHARACTER VARYING (10),
    VALUE integer);

INSERT INTO configuration
(VALUES ('id12345', 'VER', 1),
        ('id12345', 'DATE', 20190101),
        ('id12345', 'COMPANY', 55),
        ('id33333', 'VER', 2),
        ('id33333', 'DATE', 20180101),
        ('id33333', 'COMPANY', 6));

SELECT * FROM configuration;

id12345    VER     1
id1234     DATE    20190101
id12345    COMPANY 55
id33333    VER     2
id33333    DATE    20180101
id33333    COMPANY 6

UPDATE configuration
    SET value = 
        CASE parameter 
            WHEN 'VER' THEN 11
            WHEN 'DATE' THEN 2020010
        END
    WHERE deviceid = 'id12345'
        AND parameter IN ('VER', 'DATE');

SELECT * FROM configuration;

id12345   COMPANY   55
id33333   VER       2
id33333   DATE      20180101
id33333   COMPANY   6
id12345   VER       11
id12345   DATE      2020010

edited Jul 16, 2019 at 2:08

answered Jul 15, 2019 at 23:40

Deepstop

3,8372 gold badges12 silver badges23 bronze badges

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

5 Comments

lei lei Over a year ago

Thanks. If I have 4th column say "time" want to update as well with column "value", how can I do that?

Deepstop Over a year ago

In the parameter table, you would just add it after END, separated by a comma, with something like time=current_time (or current_timestamp), and if you wanted to set it only when VER or DATE were modified, then you'd would add AND parameter IN ('VER', 'DATE') to the WHERE clause.. When your table has separate columns, then practically speaking you would only be able to keep one per row.

lei lei Over a year ago

Thanks. When use your first code to update parameters, all devices get update but not only device 'id12345'.... Can you help?

Deepstop Over a year ago

There is a problem with the first query, but not the one you describe. It needs the additional clause in WHERE to prevent it from modifying company as well. However, it only modifies the rows where id='id12345' as you can see from the test I added to the answer.

lei lei Over a year ago

Thanks. I run the query through Heidi, the log I got is "/* Affected rows: 75 Found rows: 0 Warnings: 0 Duration for 1 query: 0.000 sec. (+ 0.047 sec. network) */", It is indeed only one row changed, but log shows 75 rows are affected.

Collectives™ on Stack Overflow

How to update some columns of multi rows in PostgreSQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related