UPDATE QUERY - 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 QUERY

Author

Topic

erica686
Starting Member

13 Posts

Posted - 2010-01-20 : 22:41:46

I have a table (CLIENT) where 278 of the records have a blank 'PERSON_CODE' field. I would like to replace this blank, with the max(RESPONSIBLE) from the VIEW_ALLMATTERS table. See query below:

SELECT CLIENT.ID, CLIENT.PERSON_CODE, MAX(VIEW_ALLMATTERS.RESPONSIBLE) AS Expr3
FROM CLIENT INNER JOIN
VIEW_ALLMATTERS ON CLIENT.ID = VIEW_ALLMATTERS.CLIENT_ID
GROUP BY CLIENT.ID, CLIENT.PERSON_CODE
HAVING (CLIENT.PERSON_CODE = '')

I have used update queries before, but have not used them when dealing with more than one table, and groupings.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-21 : 00:08:00

Try the script,

UPDATE CLIENT SET CLIENT.PERSON_CODE= C1
FROM CLIENT INNER JOIN
(SELECT CLIENT.ID,MAX(VIEW_ALLMATTERS.RESPONSIBLE) AS C1 FROM CLIENT INNER JOIN
VIEW_ALLMATTERS ON CLIENT.ID = VIEW_ALLMATTERS.CLIENT_ID
GROUP BY CLIENT.ID, CLIENT.PERSON_CODE
HAVING (CLIENT.PERSON_CODE = '')) AS T ON T.ID=CLIENT.ID
WHERE CLIENT.PERSON_CODE = ''

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-01-21 : 02:04:11

hi sql-programmers,

i Assume ID is unique on table.if that is the case

UPDATE CLIENT SET CLIENT.PERSON_CODE= C1
FROM CLIENT INNER JOIN
(SELECT CLIENT.ID,MAX(VIEW_ALLMATTERS.RESPONSIBLE) AS C1 FROM CLIENT INNER JOIN
VIEW_ALLMATTERS ON CLIENT.ID = VIEW_ALLMATTERS.CLIENT_ID
GROUP BY CLIENT.ID, CLIENT.PERSON_CODE
HAVING (CLIENT.PERSON_CODE = '')) AS T ON T.ID=CLIENT.ID
WHERE CLIENT.PERSON_CODE = ''

as per this(sql-programmers) WHERE CLIENT.PERSON_CODE = '' ,does this required?

just need to know abt the performance point of view please.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 05:15:16

Both of those seem a little messy. What about this?


UPDATE c SET
	[PERSON_CODE] = va.[RESPONSIBLE]
FROM
	client c

	JOIN (
		SELECT
			[CLIENT_ID] AS [CLIENT_ID]
			, MAX([RESPONSIBLE]) AS [RESPONSIBLE]
		FROM
			VIEW_ALLMATTERS
		GROUP BY
			[CLIENT_ID]
		)
		va ON va.[CLIENT_ID] = c.[ID]

WHERE
	c.[PERSON_CODE] = ''

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources