Postgres giving relation does not exist error for alias

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'; 

Answer

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