How to Swap Column Values in SQL Server 2008

How do I swap column values in sql server 2008?

UPDATE employee
SET AttributeValue = AttributeName, 
    AttributeName = AttributeValue

However, unless both columns have the exact same definition, you risk losing information.

How to Swap column values in SQL server of different datatype

Your query work for you..This is sample and give the perfect result.

declare @t table(c1 int, c2 int)

insert into @t values(10,11),(11,13),(14,15),(16,17),(18,19)
select * from @t
update @t set c1 = c2 ,c2=c1

select * from @t

Updated :- Please check my answer.

You can set dateformat in sqlserver side via "set dateformat mdy"

set dateformat dmy
select isdate( '18/12/2014' ), convert( datetime, '18/12/2014' ,103)

you can use ISDATE with convert date function.

set dateformat mdy
--the problem is in your insert not in update, if data is save in 'mm-dd-yyyy', then no issue with swap.
;with cte as (
    select 
        case when isdate('19/12/2014') = 1
             then '19/12/2014'
             else convert( datetime, '19/12/2014' ,103)
        end stdt
    from @t
)

select * from cte

--update @t set c1 = c2 ,c2=c1
--select * from @t

SQL Server 2008 Swap single column value inside and out of brackets

No, there is no special function, that switches parts of the string. You have to manually come up with something like this:

SELECT * INTO tbl_Students
FROM (VALUES
('ABC [DEF]'),
('GHI [JKL]'),
('MNO [PQR]')) as x(StudentName);
GO
SELECT * FROM tbl_Students;
GO
UPDATE tbl_Students
SET StudentName =
    REPLACE(RIGHT(StudentName, LEN(StudentName) - CHARINDEX ('[',StudentName)),']',' [')
    + REPLACE(LEFT(StudentName,CHARINDEX ('[',StudentName)-1),' ',']');
GO
SELECT * FROM tbl_Students;
GO

Swapping column values in MySQL

I just had to deal with the same and I'll summarize my findings.

1. The UPDATE table SET X=Y, Y=X approach obviously doesn't work, as it'll just set both values to Y.

2. Here's a method that uses a temporary variable. Thanks to Antony from the comments of http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ for the "IS NOT NULL" tweak. Without it, the query works unpredictably. See the table schema at the end of the post. This method doesn't swap the values if one of them is NULL. Use method #3 that doesn't have this limitation.

   UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL;

3. This method was offered by Dipin in, yet again, the comments of http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/. I think it’s the most elegant and clean solution. It works with both NULL and non-NULL values.

   UPDATE swap_test SET x=(@temp:=x), x = y, y = @temp;

4. Another approach I came up with that seems to work:

   UPDATE swap_test s1, swap_test s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;

Essentially, the 1st table is the one getting updated and the 2nd one is used to pull the old data from.

Note that this approach requires a primary key to be present.

This is my test schema:

CREATE TABLE `swap_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `x` varchar(255) DEFAULT NULL,
  `y` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `swap_test` VALUES ('1', 'a', '10');
INSERT INTO `swap_test` VALUES ('2', NULL, '20');
INSERT INTO `swap_test` VALUES ('3', 'c', NULL);

Swap values for two rows in the same table in SQL Server

If you want to swap values from one row to the other for two known IDs try something like this:

--need to store the original values
SELECT
    *,CASE WHEN id=123 then 987 ELSE 123 END AS JoinId
    INTO #Temp
    FROM YourTable
    WHERE ID in (123,987)

--swap values
UPDATE y
    SET col1=t.col1
        ,col2=t.col2
    FROM YourTable        y
        INNER JOIN #Temp  t ON y.id =t.JoinId
    WHERE ID in (123,987)

How to switch rows to columns and vice versa in SQL Server 2008

first you need to unpivot your data.. to do this, all data types much match, so you need to convert the 2 numeric columns to varchars.

still use stuff before you unpivot to get the combined values per name, date_ but use distinct to only get the name, date_ values once.

after you unpivot, you just need to pivot again.

SELECT  *
FROM (  SELECT  DISTINCT
                Name, 
                date_,
                sales_code = STUFF((SELECT ', ' + sales_code 
                                    FROM @tmpTable t2 
                                    WHERE t2.Name = t.Name AND t2.date_ = t.date_ 
                                    FOR XML PATH('')), 1, 2, ''),
                sales      = STUFF((SELECT ', ' + CONVERT(VARCHAR, sales) 
                                    FROM @tmpTable t2 
                                    WHERE t2.Name = t.Name AND t2.date_ = t.date_ 
                                    FOR XML PATH('')), 1, 2, ''),
                earned     = STUFF((SELECT ', ' + CONVERT(VARCHAR, earned) 
                                    FROM @tmpTable t2 
                                    WHERE t2.Name = t.Name AND t2.date_ = t.date_ 
                                    FOR XML PATH('')), 1, 2, '') 
        FROM    @tmpTable t) t
UNPIVOT (
    val 
    FOR category IN (sales_code, sales, earned)
) up
PIVOT (
    MAX(val)
    FOR date_ IN ([2016-08-01], [2016-08-02], [2016-08-03])
) p
ORDER BY name DESC, 
        category DESC

MySQL - Swap values of two columns in a row

As stated in the MySQL manual:

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Thus you cannot use a simple UPDATE command. Instead, you can perform a self-join:

UPDATE myTable old
  JOIN myTable new USING (id)
SET    new.start_year = old.end_year,
       new.end_year = old.start_year
WHERE  old.start_year > old.end_year

ssis swap some values in a data flow if they match a lookup table

You could use a lookup component and then:

• Set it up to Ignore Failure
• Values that do not match will return null for the lookup value

• Use a derived column expression to populate where the lookup succeeded

  ISNULL(Value2) ? Value : Value2

---

Related Topics