5 ms·
You can already achieve this with CTEs.
by idorosen 3y ago
You can already achieve this with CTEs.
- orthoxerox 3y agoCan you really run updates on CTEs that are simple projections and selections? TIL.
- williamdclt 3y agoNo, you run a SELECT … LIMIT in a CTE, and the main query is an UPDATE … JOIN CTE ON mytable.id = CTE.id
- ElongatedMusket 3y agoBut using a Common Table Expression is a workaround, kind of like using a paper towel as a plate. Just give us a plate...
- williamdclt 3y agoCompletely agree, I’d like a LIMIT on UPDATE too
- skrebbel 3y agoHow?
- CraigJPerry 3y agoWould you not just be able to do the CTE as you would a correlated sub query? Something like: WITH batch AS ( SELECT id FROM user_profile WHERE followers_count IS NULL LIMIT 10000 ) UPDATE user_profile SET followers_count = 0 FROM batch WHERE user_profile.id = batch.id But with the difference that if you didn’t want to round-trip to the application for each batch you could now make this a recursive CTE?
- layer8 3y agoDo you specifically need a CTE, or wouldn't a nested SELECT also work?