UPDATE based on results? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE based on results?

Author

Topic

mfindlay
Starting Member

5 Posts

Posted - 2008-02-06 : 14:48:13

Hello,

I'm trying to update FOR_SORTING to 1, for all instances of a RESPONSE_ID when QUESTION_ID = YESNO and RESPONSE = Yes.

So, for below I'm trying to update FOR_SORTING to 1, for RESPONSE_ID that has Question_ID that is equal to YESNO and Response equals Yes.

No. RESPONSE_ID QUESTION_ID RESPONSE FOR_SORTING
1. 1 RATING Incredible
2. 1 YESNO Yes 1
3. 2 RATING Incredible
4. 2 YESNO No

The Table is called LSN_RESPONSE_DETAILS

I tried the following, but it only updates the one row.

UPDATE LSN_RESPONSE_DETAIL
SET FOR_SORTING = '1'
WHERE QUESTION_ID = 'YESNO' and 'RESPONSE = 'Yes'

Any idea's?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-06 : 15:05:27

Based on your example data record No 2 is the only record with a QUESTION_ID = 'YESNO' and RESPONSE = 'Yes'. Your update worked correctly.

Do you want the other rows to receive a 0 like so?

UPDATE LSN_RESPONSE_DETAIL
SET FOR_SORTING = CASE 
                   WHEN QUESTION_ID = 'YESNO'
                        AND 'RESPONSE = 'Yes'
                   THEN '1'
                   ELSE '0'
                  END

mfindlay
Starting Member

5 Posts

Posted - 2008-02-06 : 15:13:54

Thank you for the fast response!

Basically what I am looking at is the results of a survey, and I am trying to add a 1 in the FOR_SORTING field for all RESPONSE_ID that are the same, and have a Yes in the RESPONSE field.

No. RESPONSE_ID QUESTION_ID RESPONSE FOR_SORTING
1. 1 RATING Incredible
2. 1 YESNO Yes 1

So for this one, I'm trying to update both rows, as long as the RESPONSE = Yes on the second row.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-06 : 15:33:29

You could do it this way:

/* Example table */
CREATE TABLE #a ( No INT, RESPONSE_ID INT, QUESTION_ID VARCHAR(15), RESPONSE VARCHAR(15), FOR_SORTING INT )
INSERT #a ( No, RESPONSE_ID, QUESTION_ID, RESPONSE, FOR_SORTING )
SELECT 1, 1, 'RATING', 'Incredible', NULL UNION
SELECT 2, 1, 'YESNO', 'Yes', NULL UNION
SELECT 3, 2, 'RATING', 'Incredible', NULL UNION
SELECT 4, 2, 'YESNO', 'No', NULL

UPDATE #a
SET FOR_SORTING = 1
FROM #a
JOIN #a a2 ON #a.RESPONSE_ID = a2.RESPONSE_ID
              AND a2.QUESTION_ID = 'YESNO'
              AND a2.RESPONSE = 'Yes'

SELECT * FROM #a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources