Обсуждение: v9.1.3 WITH with_query UPDATE

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

On 2012-06-16 19:11, Bill House wrote:
> 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)?

http://www.depesz.com/2011/03/16/waiting-for-9-1-writable-cte/

http://www.slideshare.net/davidfetter/writeable-ct-espgconmay2011

regards,
Yeb

On Jun 16, 2012, at 1:11 PM, Bill House wrote:

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

In Update clause you have to use tablename.
Syntax would be something like given below:
WITH batch as (SELECT columname,columname FROM tablename) UPDATE file_test set del=false FROM batch where
file_test.columname=batch.columnname... 

Also AFAIU, you want to set del flag to false for duplicate md5 then you can achieve this with Normal UPDATE.

Something like given below:
UPDATE file_test set del=false WHERE CTID not in (SELECT MIN(ctid) FROM file_test WHERE
md5sum='0010a3e4cc6cb8623c014f5bb95b5be1';


Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Blog: http://vibhork.blogspot.com

On 06/16/2012 01:27 PM, Vibhor Kumar wrote:
> On Jun 16, 2012, at 1:11 PM, Bill House wrote:
>
>> 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;
> In Update clause you have to use tablename.
> Syntax would be something like given below:
> WITH batch as (SELECT columname,columname FROM tablename) UPDATE file_test set del=false FROM batch where
file_test.columname=batch.columnname... 
>
> Also AFAIU, you want to set del flag to false for duplicate md5 then you can achieve this with Normal UPDATE.
>
> Something like given below:
> UPDATE file_test set del=false WHERE CTID not in (SELECT MIN(ctid) FROM file_test WHERE
md5sum='0010a3e4cc6cb8623c014f5bb95b5be1';
>
>
> Thanks & Regards,
> Vibhor Kumar
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
> Blog: http://vibhork.blogspot.com
>
>
>
Thanks for the help.

Based on your pointers and some careful re-reading of the manuals, I
composed a command that does what I want it to do (at least in my
preliminary test), mark all but one record to delete.

Here it is for the record:

    wch=# WITH batch AS (SELECT * FROM files_test
                      WHERE md5sum ='0010a3e4cc6cb8623c014f5bb95b5be1'
                      ORDER BY path DESC
                      OFFSET 1)
                  UPDATE files_test
                      SET del = True
                      FROM batch
                      WHERE batch.md5sum || batch.path =
files_test.md5sum || files_test.path;

                   UPDATE 2
      wch=#

If anyone can suggest a more efficient composition, I would be glad to
see it.  I am very new to this.

Thanks again,

Bill