Update Select Query Help - 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)

Update Select Query Help

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2009-06-29 : 09:20:45

Hi,

I think I have a simple logic issue here. I want to update a field where the record_type = company but I only want one instance where a company name exists more than once. I know if i run the Select Query alone i get the results is need. When i put it with the update statement, every record in the database gets updated.

Any help would be great. Thanks.

Query i have been trying to get working...



UPDATE wce_contact SET pager = 'Parent' WHERE EXISTS (SELECT MAX(COMPANY) AS company
FROM         wce_contact
WHERE     (Record_Type = 'Company')
GROUP BY COMPANY)

This gets the results i need updated.


SELECT MAX(COMPANY) AS company
FROM         wce_contact
WHERE     (Record_Type = 'Company')
GROUP BY COMPANY

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-06-29 : 09:26:18

You grab the max company. Also grab the primary key. Then, add that key to the where clause of your update statement.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 09:53:29

Not only the MAX company. ANY MAX company...
There is no relation between wce_contact table and Company table.

N 56°04'39.26"
E 12°55'05.63"

mshsilver
Posting Yak Master

112 Posts

Posted - 2009-06-29 : 10:00:25

Hi,

Thanks for the reply,

I tried several alternativesand can't seem to get it right. Do you mean like this?



UPDATE wce_contact SET pager = 'Parent' WHERE  MAX(COMPANY) EXISTS (SELECT MAX(COMPANY) AS company
FROM         wce_contact
WHERE     (Record_Type = 'Company')
GROUP BY COMPANY)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 10:05:17

I think you need something like this

UPDATE	f
SET	f.Pager = 'Parent'
FROM	(
		SELECT	Pager,
			ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY DateTime DESC) AS recID
		FROM	wce_contact
	) AS f
WHERE	recID = 1

Please read this blog post for us to fully assist your needs.

This is WHY you need to read
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

This is HOW you provide data
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources