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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
