0
WITH TAB AS 
(
 SELECT id,flag FROM source where flag IS NULL and Data= '151234567890' ORDER BY 1 OFFSET 
 (
  SELECT COUNT(*) FROM source where flag IS NULL and Data = '151234567890' ) - 96 FETCH FIRST 96 ROWS ONLY )
 UPDATE TAB SET flag = 'Z';

Above query is working fine in MS Sql but showing this error when running it in postgres

ERROR:  relation "tab" does not exist
LINE 9:  UPDATE TAB SET flag = 'Z';
Yash Gupta
  • 13
  • 3
  • Does this answer your question? [Update with result from cte (postgresql)](https://stackoverflow.com/questions/36908495/update-with-result-from-cte-postgresql) – Stu Feb 04 '22 at 09:07
  • a CTE isn't really a table and therefore cannot be updated. Join the result of your CTE with the origin table to identify the records you want to updated and then perform the `UPDATE`. – Jim Jones Feb 04 '22 at 09:09

1 Answers1

0

In MS Sql Server it's possible to update the CTE.

In Postgresql you can link to a CTE for an update.

For example:

WITH CTE AS (
  SELECT id FROM source
  WHERE Data= '151234567890' 
   AND flag IS NULL
  ORDER BY id DESC
  FETCH FIRST 96 ROWS ONLY
) 
UPDATE source t
SET flag = 'Z'
FROM CTE c
WHERE t.id = c.id;

However, such update assumes that the id in that table is unique.

But, to mark the newest duplicates it can be simplified.

UPDATE source tgt
SET flag = 'Z'
FROM source src
WHERE src.Data = '151234567890'
  AND tgt.Data = src.Data
  AND tgt.id = src.id
  AND tgt.ctid > src.ctid
  AND tgt.flag IS NULL;

Or if you want to flag the higher amounts.
Using ROW_NUMBER could help.

WITH CTE AS (
  SELECT ctid, id, Data, Amt
  , ROW_NUMBER() OVER (PARTITION BY Data, id ORDER BY Amt) AS rn
  FROM source
  WHERE Data= '151234567890' 
   AND flag IS NULL
) 
UPDATE source t
SET flag = 'Z'
FROM CTE c
WHERE c.rn > 1
  AND t.id = c.id
  AND t.Data = c.Data
  AND t.ctid = c.ctid;

Test on db<>fiddle here

LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • Thanks for quick response I tried this but this updates the all 100 rows i have but i want to update only 96 rows. – Yash Gupta Feb 04 '22 at 10:14
  • I just want to update only fetched 96 rows with inner query. NOT ALL 100 ROWS exist in table. – Yash Gupta Feb 04 '22 at 10:22
  • Seems to do the same according the simplified test in [this fiddle](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=50852223168d09117b1a804b7b221812). Except that the more complicated update will crash when the offset becomes negative. – LukStorms Feb 04 '22 at 10:32
  • You have consider id as primary key, but in my case id is not a primary key and four ids are unique like 1,2,3,4 and remaining 96 are duplicate like 1.....1, 2......2, 3......3, 4....4 so i want to skip first four unique ids and update remaining to 'Z' for 96 duplicate rows. – Yash Gupta Feb 04 '22 at 10:45
  • Ok, with dup id's. So you basically just want to flag the dups as 'F'? – LukStorms Feb 04 '22 at 11:13
  • correct, i want to update dupids only. – Yash Gupta Feb 04 '22 at 11:16
  • This reminds me of [this](https://stackoverflow.com/a/70963521/4003419) recent other question I solved, but was for deletions. – LukStorms Feb 04 '22 at 11:17
  • @YashGupta See update. – LukStorms Feb 04 '22 at 11:52
  • In this it skips the last record and updates the other but I want the first one as unique and other should update to Z. see this fiddle [link](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=c2ed889bb5ffe323c932310ffa762058) in this I want id-1,amt-100 as unique and its duplicate which have amt 200 as Z. – Yash Gupta Feb 04 '22 at 12:47
  • Changed the `<` to `>`, so it won't flag the lowest ctid. And added an alternative that uses row_number. But to be honest, this would be less complicated if you added a column with an [IDENTITY](https://www.postgresqltutorial.com/postgresql-identity-column/). – LukStorms Feb 04 '22 at 13:08