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