February 6, 2007 at 2:00 pm
Hello,
I have a data base with the following information:
Customer ID Question Name Question Answer
000000001 Question 1 Farm
000000001 Question 2 2
000000001 Question 3 Grain
000000002 Question 1 Residential
000000002 Question 2 2
000000002 Question 3 Potatoes
If I wanted to go and change the Question Answer for Question 2 to 3 for any one who answered Farm on Question 1 how would I go about doing that? I understand this was a really bad way to set up a database, I'm just maintaining it. If you have any ideas please let me know.
February 6, 2007 at 2:32 pm
UPDATE UPD
SET Question Answer = 3
FROM TableName UPD
JOIN (SELECT CustomerID
FROM TableName
WHERE QuestionNAme = 'Question 1'
AND Question Answer = 'Farm'
) As LIST
ON UPD.CustomerId = LIST.CustomerID
WHERE UPD.QuestionName = 'Question 2'
February 6, 2007 at 2:38 pm
I think I get what you're trying to do. By selecting as LIST does it make a temporary table? In your select you have a TableName, which table would that be?
February 6, 2007 at 9:35 pm
You can JOIN to a temporary result set which must be named. In this case I called it LIST. You can call it anything you like. The data may be created in atemporary table on the Tempdb or in memory depending on the size of the result set.
The TableName is the SQLServer table that contains the data you described in your question.
Michael.
February 6, 2007 at 10:37 pm
This is where I'm confused. You have FROM TABLENAME UPD
I thought UPD was the table that contained the information that I needed changing. Did you mean just FROM UPD?
February 7, 2007 at 6:35 am
This is the syntax for updating with a JOIN. Pretend it is a SELECT statement. Then instead of the SELECT clause, you specify UPDATE alias, SET Columns......
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply