v9.1.3 WITH with_query UPDATE - Mailing list pgsql-general

Hello all,

Would someone please point me to (or supply) some working examples of
UPDATE commands using the WITH clause syntax as described in the manual
(pdf version page 1560) and referring to Section 7.8 (pdf version page 104)?

I have looked around a lot and haven't seen much on this.

I have a table laid out as follows:

      wch=# \d files_test
            Table "public.files_test"
       Column |      Type      |
Modifiers


--------+----------------+-----------
              

     md5sum | character(33)
|

     path        | character(475)
|

     file           | character(200)
|

     del           | boolean
|

     recno      | integer              | not null

Indexes:
              

          "files_test_ord" btree (recno)

md5sum may be duplicated and I am trying to mark the column "del" of the
redundant records leaving one unmarked.

Here is one variation of the syntax I have tried on one group:

      WITH batch AS (select * from files_test where
              md5sum =  '0010a3e4cc6cb8623c014f5bb95b5be1'
             ORDER BY path DESC OFFSET 1)
      UPDATE batch SET del = False;

The error message I get in this instance is:

      ERROR:  relation "batch" does not exist
      LINE 1: ...4f5bb95b5be1' ORDER BY path DESC OFFSET 1) UPDATE batch
SET ..

Thanks for your help,

Bill