PostgreSQL – Update Query with Two Different WHERE Conditions

postgresqlupdate

I would like to set status to false for all rows except one, which should should be set to true. How can I achieve that?

Here is the query I tried:

UPDATE students set status = false where status = true and 
set status = true where _id = 1;

Best Answer

You can use a case expression:

UPDATE students 
    set status = CASE WHEN _ID = 1 THEN true
                                   ELSE false
                 END;

A bit shorter (but perhaps not as obvious) is:

UPDATE students 
    set status = COALESCE(_ID = 1,false);

COALESCE is need if _ID can be NULL.

One may argue that it is not necessary to update status at all times (for efficiency reasons). If we take into consideration that both _ID and status can be null there are 9 different situations (we let _ID = 0 represent all rows where _ID is not null and _ID <> 1).

_ID    Status    Action
-----------------------
null   null      set false
null   true      set false
null   false     -
   0   null      set false
   0   true      set false
   0   false     -
   1   null      set true
   1   true      - 
   1   false     set true

All Actions is handled by:

set status = COALESCE(_ID = 1,false);

So we need to filter out the 3 situations where no update should take place:

(null, false), (0, false), (1, true)

In other words, our WHERE clause should include the other 6 combinations:

UPDATE students 
    set status = COALESCE(_id = 1,false)
WHERE ((coalesce(_id, 0) = 1) <> coalesce(status,(coalesce(_id, 0) <> 1)))

This is not trivial (IMO), and even though it probably can be simplified I would not recommend to use it in any real situation (I would recommend disallowing nulls for boolean attributes in the first place, but that's another story).

A useful technique (once again IMO) to verify boolean expressions with truth tableau's, is to use CTE's for the domains and investigate the result. Example from above:

with status (s) as ( values (null),(false),(true))
  ,     _id (i) as ( values (null),(0),(1))
select i, s from status cross join _id
where ((coalesce(i, 0) = 1) <> coalesce(s,(coalesce(i, 0) <> 1)));

IS DISTINCT FROM as demonstrated by @Erwin Brandstetter is a nice abstraction that is very useful in situations like these.

Related Solutions

PostgreSQL – Update New Column with Value from Old Columns

The solution is very simple. If you do

UPDATE exchange
SET emp_id = {some expression here on the params column}; -- no WHERE clause!

it will do exactly what you want: pick the params from the row to be updated, calculate the expression and update emp_id with its value. As there is no WHERE clause, it will go through the whole table. You don't need the ID of the row or anything else.

Oracle 11g – Updating Duplicate Records with Different Data

Using the ranking ROW_NUMBER() function will work. First give row numbers to all rows in both tables, then join using these row numbers, then update:

with 
  oldt as
  ( select fileNo , folder, fileType,
           row_number() over (partition by fileNo, folder
                              order by fileType)
             as rn
    from oldtable
  ),

  newt as
  ( select fileNo , folder, fileType,
           row_number() over (partition by fileNo, folder
                              order by fileType)
             as rn
    from newtable
  ),

  upd as
  ( select 
        n.fileType,
        o.fileType as old_fileType
    from newt n
      join oldt o
      on  n.fileNo = o.fileNo
      and n.folder = o.folder
      and n.rn     = o.rn 
  ) 

update
    upd
set 
    fileType = old_fileType ;

SQLfiddle seems to be giving error for Oracle, so it has been tested in SQL Server only: SQLfiddle-test (but this syntax should be valid for Oracle, too).

Tested in Oracle, the above doesn't work, sadly. I think because statements that start with WITH can only be SELECT statements. Even if the query is rearranged (I tried several rewrites), Oracle throws various errors. The only way I managed to have it working is after adding another column in newtable and a unique constraint on it. Then the following works (nid is the added primary key column).

Tested in Oracle's Live SQL site:

update 
  ( with 
      oldt as
      ( select fileNo , folder, fileType,
               row_number() over (partition by fileNo, folder
                                  order by fileType)
                 as rn
        from oldtable
      ),
    newt as
      ( select fileNo , folder, nid,
               row_number() over (partition by fileNo, folder  
                                  order by fileType)
                 as rn
        from newtable
      ),
    upd as
      ( select 
            n.nid,
            o.fileType as old_fileType
        from newt n
          join oldt o
          on  n.fileNo = o.fileNo
          and n.folder = o.folder
          and n.rn     = o.rn 
      ) 
    select 
        up.fileType,
        ( select upd.old_fileType
          from upd 
          where upd.nid = up.nid 
        ) as old_fileType
    from newtable up
  ) x
set fileType = old_fileType ;

Best Answer

Related Solutions

PostgreSQL – Update New Column with Value from Old Columns

Oracle 11g – Updating Duplicate Records with Different Data

Related Question