6

I want to swap values of two columns in a table, And I found that in SQL we can do that by using Update:

update the_table set first_name = last_name, last_name = first_name;

It works But I wonder How SQL can do that without overwrite data in a column of other column?

6
  • 1
    Are you asking how it can physically be done that way, or are you asking what in the statement implies that it should be done that way? Or are you asking for confirmation that it does always work? Sep 28, 2022 at 10:22
  • @Charlieface I would like to get answers to all of these three questions you provide.
    – XMehdi01
    Sep 28, 2022 at 10:39
  • Not unique to SQL, you can do the same in python Sep 28, 2022 at 12:25
  • @Lennart-SlavaUkrainin But in Python you either need a temporary variable or a temporary tuple value
    – Bergi
    Sep 28, 2022 at 12:49
  • You can do (a, b) = (b, a) in python @Bergi. Sep 28, 2022 at 14:22

3 Answers 3

10

@mustaccio has answered how this is physically implemented.


The logical specification is as follows:

The columns on the right side of the = in an UPDATE SET statement must come from the values before the update is applied. Therefore it is simple to swap the values, because the right side always refers to the old values.

Another way to see why is to consider the equivalent rewriting of your statement:

update the_table 
set (first_name, last_name) = (last_name, first_name);

This is not implemented by all SQL products but it is equivalent according to the SQL standard and where it has been implemented, it works exactly as yours.


As to whether it is guaranteed: yes it is. It is mandated by the SQL specification, and as far as I know, most DBMSs implement this requirement (MySQL and derived/forked products eg MariaDB are an exception and do not implement the standard correctly in this case).

1
6

It's because each row is processed in its entirety, as a whole, not column by column. The simplified sequence goes like this:

  1. Fetch a matching row.
  2. Retrieve current values of the referenced columns.
  3. Update columns as requested.
  4. Write the row back.
  5. Go to 1.

The SQL standard (at least the version I have access to) stipulates the behaviour in these terms (emphasis mine):

  1. S[et]C[lause] is evaluated for each row of T[able] prior to the invocation of any <triggered action> caused by the update of any row of T.
  2. The <update source> of each <set clause> contained in S[et]C[lause]L[ist] is effectively evaluated for each row of T before any row of T is updated.
  3. For each subject row, a candidate new row is constructed by copying the subject row and updating it as specified by each <set clause> contained in SCL by applying the General Rules of Subclause 14.15, “<set clause list>”.
4
  • I like the explanation but the simplification would fail to explain a similar question about swapping values between two rows. (perhaps add a note?) Sep 28, 2022 at 12:31
  • @ypercubeᵀᴹ Why would that be any different? If you have a self-join to pair the rows, it will retrieve all the columns from the source row (step 2), then update the columns in the destination row (step 3 and 4).
    – Barmar
    Sep 28, 2022 at 14:12
  • @Barmar, it shouldn't be different and it isn't in practice. But the "simplified sequence" as described here seems to be imply that the changes are applied row by row. (we modify row A with the value from row B, write the changes back to disk and then proceed to modify row B). That wouldn't work as wanted. Sep 28, 2022 at 14:19
  • @ypercubeᵀᴹ Ah right, I was thinking "assign", not "swap".
    – Barmar
    Sep 28, 2022 at 14:22
0

I have used the answers above but none of them are working. Because it automatically updates one and copies the same value to the other. It can be related to the version of the SQL we are using.

I recommend creating an empty column to use as an intermediate copy buffer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.