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

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

CodePudding user response：

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);

CodePudding user response：

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.