On 6/8/2017 6:36 PM, marcinha rocha wrote:
UPDATE tablea a SET migrated = yes WHERE a.id = row.id;
On my original select, the row will have migrated = false. Maybe All I need to put is a limit 2000 and the query will do the rest?
SELECT does not return data in any determinate order unless you use an ORDER BY.... so LIMIT 2000 would return some 2000 elements, not neccessarily the 'first' 2000 elements unless you somehow order them by however you feel 'first' is defined.
WITH ids AS (INSERT INTO tableb (id) SELECT id FROM tablea WHERE migrated=FALSE ORDER BY id LIMIT 2000 RETURNING id)
UPDATE tablea a SET a.migrated=TRUE WHERE a.id = ids.id RETURNING COUNT(a.id);
I'm not 100% sure you can do UPDATE .... RETURNING COUNT(...), worse case the UPDATE RETURNING would be a subquery of a SELECT COUNT()...
--
john r pierce, recycling bits in santa cruz