Need to switch values in a column containing certain values.

Question

Post reply

Need to switch values in a column containing certain values.

Trystan Hughes

SSCrazy

Points: 2109
More actions
October 3, 2006 at 7:44 am

#113865

Hi all,
I have a column named QuestionNodeTypeUID that contains ID's that point to a Lookup table. What I need to do is change the rows that have the column value of 6 to 5 and the rows that have the column value of 5 to 6.
Is it possible to perform this update in one simple query? I tried to use the CASE statement but the ELSE part of it cannot do tests (like in the WHEN).
(I can't use temp tables)
Thanks in advance.
Tryst

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

You should be able to use a CASE statement. Have a look at the example I cooked up below...

CREATE TABLE #test(QuestionNodeTypeUID INT NOT NULL,

description VARCHAR(50))

INSERT INTO #test (QuestionNodeTypeUID,description) VALUES (5, 'This should END up AS 6') INSERT INTO #test (QuestionNodeTypeUID,description) VALUES (5, 'This should END up AS 6') INSERT INTO #test (QuestionNodeTypeUID,description) VALUES (3, 'This should not change') INSERT INTO #test (QuestionNodeTypeUID,description) VALUES (6, 'This should END up AS 5') INSERT INTO #test (QuestionNodeTypeUID,description) VALUES (6, 'This should END up AS 5') INSERT INTO #test (QuestionNodeTypeUID,description) VALUES (5, 'This should END up AS 6')

SELECT * FROM #test

UPDATE #test

SET QuestionNodeTypeUID = CASE WHEN QuestionNodeTypeUID = 5 THEN 6

WHEN QuestionNodeTypeUID = 6 THEN 5

ELSE '' END -- You could add some error or something here for if something went wrong.... add a 'err' or something so that the update staement blows up and doesn't complete. Wrap it in a transaction so it rolls back etc...

WHERE QuestionNodeTypeUID BETWEEN 5 AND 6

SELECT * FROM #test

DROP TABLE #test

To help us help you read this[/url]For better help with performance problems please read this[/url]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Tryst

Here's a way of doing it without a CASE statement

--Data

CREATE

TABLE #MyTable (TableID int, QuestionNodeTypeUID int)

INSERT

INTO #MyTable VALUES (1, 4)

INSERT

INTO #MyTable VALUES (2, 5)

INSERT

INTO #MyTable VALUES (3, 6)

--Query

UPDATE

#MyTable

SET

QuestionNodeTypeUID = QuestionNodeTypeUID - SIGN(2 * QuestionNodeTypeUID - 11)

WHERE

QuestionNodeTypeUID IN (5, 6)

--Results

SELECT

* FROM #MyTable

John

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 3

Thanks for the replies guys.

John: That is a very neat way of doing it. So can that query be applied to all cases where you need to convert/switch values (so long as the values you need to switch are in the IN clause)?

Thanks

Tryst

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Tryst

Yes, so long as it as simple as switching two values over. Obviously if the values were 13 and 28, say, then you'd need to think about how you would rewrite that update statement.

John