CODEWITHSUNDEEP
postgresqlsql-updateupdates
c3win90
c3win90

Reputation: 69

Column is ambiguous when doing an update in Postresql

I am trying to update the table if min_failed from bx_temp is smaller than the current min_failed value in the table.

I keep getting a "min_failed is ambiguous" referring to the min_failed in my case statement. I can't assign it a table alias either though.

  with c as (select * from b_temp)
    update table b 
    set min_failed = (select case when ct.min_failed < min_failed then ct.min_failed else min_failed end)
    from c
    where c.user = b.user

Upvotes: 0

Views: 616

Answers (1)

Nick
Nick

Reputation: 7451

I'm not able to test the query, but you can modify it like so:

WITH c AS (SELECT * FROM b_temp)
UPDATE table b 
SET min_failed = LEAST(c.min_failed,b.min_failed)
-- Use LEAST() instead of case logic. Also fixes your error because you were using SELECT without a FROM
FROM c
WHERE c.user = b.user;

Upvotes: 2

Related Questions