To resolve a problem, sometime we start thinking at high level while, simple solutions of said problem are available. This is what happened to me, when one of my colleagues (Tehman) asked me how to swap two column values in a table.
My answer was, create a third column (temporary) and swap using this third column, which you can remove later on. Here was the plan.- Move Col2 data to Col3
- Move Col1 data to Col2
- Move Col3 data to Col1
- Drop Col3
-- Create Temporary table to hold values
CREATE TABLE #ForSwappingTest ( Col1 VARCHAR(50), Col2 VARCHAR(50))-- Insert test reocrds
INSERT INTO #ForSwappingTest (Col1,Col2)
VALUES ('A','X'),
('B','Y'),
('C','Z')
-- Check Results
SELECT * FROM #ForSwappingTest
-- Add third column to hold data temporarily
ALTER TABLE #ForSwappingTest ADD Col3 VARCHAR(50)
-- Start Swaping
UPDATE #ForSwappingTest
SET COL3 = COL2
UPDATE #ForSwappingTest
SET COL2 = COL1
UPDATE #ForSwappingTest
SET COL1 = COL3-- Remove additional temporary column
ALTER TABLE #ForSwappingTest DROP COLUMN Col3
--Drop temporary table when not required
DROP TABLE #ForSwappingTest
But he came with a very simple solution, by writing following simple query.
UPDATE #ForSwappingTestSET Col2 = Col1,
Col1 = Col2
Hi, Can I interchange the column with values?
ReplyDeleteThat would be just like updating columns with same values. UPDATE table SET Col2= 123. (Never try on production, as it will update all columns with same values).
Delete