help with an update statement - 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

SQL Server 2005 Forums

Transact-SQL (2005)

help with an update statement

Author

Topic

-Dman100-
Posting Yak Master

210 Posts

Posted - 2008-12-05 : 12:55:50

I am trying to update a table with 5,000+ rows.

Here are two example rows:


ID	NAME	OLD_ACCOUNT_NAME__C	NEW_ACCOUNT_NAME__C	MERGE_TARGET__C	PID__C
4907	5018788	Page Ms (Edpa)		                        Yes	        999267
309	141932	Page Middle School		                No	        999267

I want to update the 'NEW_ACCOUNT_NAME__C' field where the 'MERGE_TARGET__C' field equals 'NO' with the the value of the 'OLD_ACCOUNT_NAME__C' field where the value of the 'MERGE_TARGET__C' field equals 'YES' and the PID__c fields match.

So, in the example above, the row with id 309 would have the value "Page Ms (Edpa)" inserted into the 'NEW_ACCOUNT_NAME__C' field.

I was trying something along these lines:

UPDATE Extract
SET NEW_ACCOUNT_NAME__C = (SELECT OLD_ACCOUNT_NAME__C FROM Extract WHERE MERGE_TARGET__C = Yes)
WHERE -- not sure how to group the records where the PID matches?

Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 13:00:41

[code]UPDATE e1
SET e1.NEW_ACCOUNT_NAME__C =e2.OLD_ACCOUNT_NAME__C
FROM Extract e1
INNER JOIN Extract e2
ON e1.PID__C=e2.PID__C
AND e1.MERGE_TARGET__C='No'
AND e2.MERGE_TARGET__C='Yes'
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources