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