Menu
SET Col1 = Col2, Col2 = Col1; When you run above update statement, the values of the columns will be swapped in SQL Server. There is no need for temporary column, variable or storage location in SQL Server.
No need for multiple statements or variables, this can be done in a single statement: update emp set phone_number = case when 205 then (select phone_number from emp where employee_id = 209) when 209 then (select phone_number from emp where employee_id = 205) end where employee_id in (205, 209);
Here, as you are a SQL Server DBA, you can simply swap them using a single update statement. Example and Application features : It happens that SQL user might enter incorrect values in the database columns, the next task is to swap those values. Syntax : Syntax to write a query to swap column values in SQL server.
As a result of a mistake during an import to the a test table called CUSTOMERS, I found myself needing to switch the values of two columns in SQL Server.
I mistakenly imported the respective values for LASTNAME and FIRSTNAME into the opposite fields (i.e. the customer last name was imported into first name, and vice versa). To remedy this, I ran the following query in SQL Server 2008 R2, of course not expecting it to work:
UPDATE CUSTOMERS
SET LASTNAME=FIRSTNAME, FIRSTNAME=LASTNAME
Surprisingly, it worked! The limited programming experience I've had (high school, a few college courses) always followed the paradigm that switching two values required the presence of a third variable to "hold" the value of one of the initial values. In other words, I expected to have to run the following query:
UPDATE CUSTOMERS
SET SOMEOTHERFIELD = LASTNAME
SET LASTNAME = FIRSTNAME
SET FIRSTNAME = SOMEOTHERFIELD
Is this behavior only seen in SQL Server 2008 R2, or does this represent some other underlying facet of relational theory that I'm missing?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With