Re: v9.1.3 WITH with_query UPDATE

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