All geek questions in one place

Does the order of the fields in the SQL update?

It seems to me that if you have fields that depend on each other in the update instructions, I’m not sure that you can guarantee the order (or the one you need!).

As an example, let's say that you have the following update:

UPDATE Table SET NewValue = OldValue, OldValue = NULL

Will NewValue always be updated first, then OldValue will be canceled? Or the state of a row (or set or table, etc.) is unchanged during processing so that all changes are not committed until the changes have been calculated?

+4
source share
3 answers

A new virtual row is created, then it automatically replaces the existing row. You have access to all existing values ​​until the data is committed.

Change By the way, this is not an unusual situation.

+6
source

Yes, the system will update NewValue to the value that existed in OldValue before executing the request, and then set OldValue to null. In fact, you can change the values ​​as follows:

 UPDATE Table SET NewValue = OldValue, OldValue = NewValue
+4
source

Why don't you just run this as two separate queries?

 begin transaction UPDATE Table SET NewValue = OldValue UPDATE Table SET OldValue = NULL commit

Or is this homework?

0
source

Source: https://habr.com/ru/post/1308187/

More articles:


All Articles