Order by in update statement oracle

You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels.

And of course, keep up to date with AskTOM via the official twitter account. Check out Connor McDonald's complete video course. Questions Update table rows in prefered row order using sequence. Question and Answer. You Asked Hi Tom, is it possible to update table rows in prefered row order by using sequence value?

A reader, September 18, - pm UTC. Learn more. Asked 3 years, 7 months ago. Active 3 years, 7 months ago. Viewed 3k times. I've tried searching for a similar issue but cant find anything. Any help is much appreciated! Improve this question. David David 73 1 1 silver badge 6 6 bronze badges.

You need to create some fake data similar to yours, some example Add a comment. Active Oldest Votes. Improve this answer. Peter M Peter M 5 5 bronze badges. Viewed 55k times. So, need to update from the end rather than from the beginning. Improve this question. Community Bot 1 1 1 silver badge. Please explain what you are trying to achieve. Is id the primary key? Updated the first post. This simplifies the query a lot updated answer, workaround not in a single trasnaction! The core problem with a non-deferrable constraint remains the same.

Add a comment. Active Oldest Votes. Improve this answer. Athafoud 2, 2 2 gold badges 39 39 silver badges 53 53 bronze badges. Can it be somehow proofed with some kind of logs or execution plan? I faced several time issues when same, or very similar update statement on many rows, executed parallel in different transactions, visits rows in different order and causing deadlock.

Workaround Updated after question update. I'd advice not to use SQL key words as identifiers, even though that's allowed. Avoid the problem On a bigger scale or for databases with heavy concurrent load, it's wiser to use a serial column for relative ordering of rows.

Consider this related answer: Is it possible to use a PG sequence on a per record label? Erwin Brandstetter Erwin Brandstetter k gold badges silver badges bronze badges.

Being able to specify a consistent locking order for UPDATE would be a big win for performance in some workloads - the sorting would cost, but not remotely as much as all the query retries.

Thanks for the concept. But your code doesn't work.