Re: v9.1.3 WITH with_query UPDATE

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