Postgresql: Update column from select and add condition when multiple rows returned

Question

Basically, I need to update a column using a SELECT, which can return more than one value. If that happens, I'd like to apply a second condition to determine which of those values is to be chosen:

UPDATE train
SET var1 = (
    CASE
        WHEN (SELECT COUNT(*)
              FROM cars
              WHERE (train.var2 LIKE cars.var2))
             > 1)
        THEN (
            SELECT var1
            FROM cars
            WHERE (train.var2 LIKE cars.var2)
              AND cars.var2 in (
                SELECT var2
                FROM cars
                WHERE train.user_id = cars.user_id)
        )
        ELSE (
            SELECT var1
            FROM cars
        WHERE (train.var2 LIKE cars.var2))
        )
    END
);

I think the above works, but I repeat 3 times the same SELECT. Do you have a nice way to avoid that? Maybe there is a simple way to catch when the select returns more than one value and do something about it?

Thank you

Abelisto · Accepted Answer · 2022-01-05 18:15:11Z

2

update train set
  var1 = (
    select cars.var1
    from cars
    where train.var2 like cars.var2
    order by train.user_id = cars.user_id desc
    limit 1);

answered Jan 5, 2022 at 18:15

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

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

2 Comments

Abelisto Over a year ago

@Alexandre Just realized it. There is a logical difference between your and my solutions. If there are several rows where train.var2 like cars.var2 but no one where train.user_id = cars.user_id then your solution returns NULL and my returns first random row. So be careful. And good luck )

Alexandre Over a year ago

Thanks, I managed to skip that difference when using the solution in my project :)

Richard Wheeldon · Accepted Answer · 2022-01-05 19:43:45Z

1

The above answer is good and works out of the box. If you do a lot of these, take a look at: https://wiki.postgresql.org/wiki/First/last_(aggregate)

Then you can do this:

update train set
  var1 = (
    select first(cars.var1 order by train.user_id = cars.user_id desc)
    from cars
    where train.var2 like cars.var2
  );

Depending on your exact use-case this may be neater, easier to read, easier to reason about (order by in subselect is full of nasty edge-cases) or just more faff than it's worth.

answered Jan 5, 2022 at 19:43

Richard Wheeldon

1,14717 silver badges30 bronze badges

1 Comment

Alexandre Over a year ago

I went with @Abelisto option in the end as IntelliJ didn't recognise 'first' for Postgres and I didn't want to look into it :) Thank you anyways.

Collectives™ on Stack Overflow

Postgresql: Update column from select and add condition when multiple rows returned

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related