CODEWITHSUNDEEP
postgresql
lei lei
lei lei

Reputation: 1829

How to update some columns of multi rows in PostgreSQL

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?

Upvotes: 0

Views: 61

Answers (1)

Deepstop
Deepstop

Reputation: 3807

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

Upvotes: 1

Related Questions