error when updating - 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 2000 Forums

SQL Server Development (2000)

error when updating

Author

Topic

yaditzal
Starting Member

22 Posts

Posted - 2010-04-05 : 09:57:57

Hello.
I need help with this query.

My problem is:
I have a table with more than one ADDR for the same client, so I need to set as primary the firth one.

please any help with this.

UPDATE PROVIDER_LOCATION_IN
SET PRIMARY_YN = 'Y'

WHERE [LOCATION_ID]= max([LOCATION_ID])
GROUP BY PROVIDER_ID

PD: SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 10:01:07

[code]
UPDATE pl
SET pl.PRIMARY_YN = 'Y'
FROM PROVIDER_LOCATION_IN pl
INNER JOIN (SELECT PROVIDER_ID,max([LOCATION_ID]) AS MaxLoc
FROM PROVIDER_LOCATION_IN
GROUP BY PROVIDER_ID) pl1
ON pl1.MaxLoc= pl.[LOCATION_ID]
AND pl1.PROVIDER_ID = pl.PROVIDER_ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/