Mass 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)

Mass Update query

Author

Topic

LukeD
Starting Member

5 Posts

Posted - 2011-02-23 : 05:36:14

I have to update the working 'sector' of a set of organisations that exist within my CRM. The desired outcome is as follows:

If Org A has a NULL value within its' Sector1Field then update Sector1Field within the given SectorValue.

Else, if there already exists a SectorValue(that is not NULL) within Sector1 field - UPDATE Sector2 field with the given 'SectorValue'.

I have attempted to do this using a CASE expression within my update query:

SELECT industrycode
where name = 'OrgA'
case
(when (industrycode = null)
UPDATE GRI_MSCRM.dbo.FilteredAccount
SET industrycode =
(SELECT AttributeValue
FROM GRI_MSCRM.dbo.StringMap
WHERE AttributeName = 'industrycode'
AND ObjectTypeCode = '1'
AND Value = 'SectorValue'),
modifiedby = 'F8FF95D4-FA5F-DE11-8C9D-0003FFC02DC1',
modifiedonutc = GETDATE()
WHERE name = 'OrgA' else
UPDATE GRI_MSCRM.dbo.FilteredAccount
SET tpo_sector2 =
(SELECT AttributeValue
FROM GRI_MSCRM.dbo.StringMap
WHERE AttributeName = 'tpo_sector2'
AND ObjectTypeCode = '1'
AND Value = 'SectorVale'),
modifiedby = 'F8FF95D4-FA5F-DE11-8C9D-0003FFC02DC1',
modifiedonutc = GETDATE()
WHERE name = OrgA)end
from dbo.FilteredAccount

I recieve the following errors which leave me to believe the structure of the query is fundamentally incorrect:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'case'.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'else'.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near ')'.

Any ideas?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-23 : 05:53:29

your syntax is all wrong. I don't really understand your query in order to suggest any changes.

Maybe you can start by posting your table DDL, sample data and expected result.

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-23 : 07:22:38

Agree -- it's really unclear what you are trying to do. I *think* it might be this????




-- CASE where [industryCode] IS NOT NULL
-- Note you need to do this first.
DECLARE @tpoSector VARCHAR(255)
SELECT @tpoSecore = [AttributeValue]
FROM GRI_MSCRM.dbo.StringMap 
WHERE
	AttributeName = 'tpo_sector2' 
	AND ObjectTypeCode = '1' 
	AND Value = 'SectorVale'

UPDATE dbo.FilteredAccount SET
	[tpo_sector2] = @tpoSector
	, [modifiedby] = 'F8FF95D4-FA5F-DE11-8C9D-0003FFC02DC1'
	, [modifiedonutc] = GETDATE()
WHERE
	[name] = 'OrgA'
	AND [industryCode] IS NOT NULL

	
-- CASE where [industryCode] IS NULL
DECLARE @nullReplaceIndustryCode VARCHAR(255)

SELECT @nullReplaceIndustryCode = [AttributeValue]
FROM GRI_MSCRM.dbo.StringMap 
WHERE
	AttributeName = 'industrycode' 
	AND ObjectTypeCode = '1' 
	AND Value = 'SectorValue'

UPDATE dbo.FilteredAccount SET
	[industrycode] = @nullReplaceIndustryCode
	, [modifiedby] = 'F8FF95D4-FA5F-DE11-8C9D-0003FFC02DC1'
	, [modifiedonutc] = GETDATE()
WHERE
	[name] = 'OrgA'
	AND [industryCode] IS NULL

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

LukeD
Starting Member

5 Posts

Posted - 2011-02-28 : 04:51:14

Hey, Thanks for the query.

Your query would imply that you indeed understand what I am trying to do, but I shall clarify in general terms:

My Organisations can be associated to two business sectors (Energy, Automotive, Finance etc.) therefore there exists 2 sector fields within my view. I wish to update the sector fields within this view such that, if an organisation already has a SectorValue within the 'Sector1' field then the 'Sector2' field will be updated with the SectorValue. If however there is no value within Sector1 field i.e (NULL), then the Sector1 field will be updated with the Sector Value.

I need to do this for many organisations hence the 'mass update', but the query will be duplicated for each organisation that needs to be updated.

The query executed:

CASE where [industryCode] IS NOT NULL
DECLARE @tpoSector VARCHAR(255)
SELECT @tpoSector = [AttributeValue]
FROM GRI_MSCRM.dbo.StringMap
WHERE --
AttributeName = 'tpo_sector2'
AND ObjectTypeCode = '1'
AND Value = 'SectorValue'

UPDATE dbo.FilteredAccount SET
[tpo_sector2] = @tpoSector
, [modifiedby] = 'F8FF95D4-FA5F-DE11-8C9D-0003FFC02DC1'
, [modifiedonutc] = GETDATE()
WHERE
[name] = 'OrgA'
AND [industryCode] IS NOT NULL

CASE where [industryCode] IS NULL
DECLARE @nullReplaceIndustryCode VARCHAR(255)
SELECT @nullReplaceIndustryCode = [AttributeValue]
FROM GRI_MSCRM.dbo.StringMap
WHERE
AttributeName = 'industrycode'
AND ObjectTypeCode = '1'
AND Value = 'SectorValue'

UPDATE dbo.FilteredAccount SET
[industrycode] = @nullReplaceIndustryCode
, [modifiedby] = 'F8FF95D4-FA5F-DE11-8C9D-0003FFC02DC1'
, [modifiedonutc] = GETDATE()
WHERE
[name] = 'OrgA'
AND [industryCode] IS NULL

Having just executed the above query I keep hitting an error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'CASE'.

Any further help with this is greatly appreciated, thanks again.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2011-02-28 : 16:13:44

quote:
Originally posted by LukeD

I have to update the working 'sector' of a set of organisations that exist within my CRM. The desired outcome is as follows:

If Org A has a NULL value within its' Sector1Field then update Sector1Field within the given SectorValue.

Else, if there already exists a SectorValue(that is not NULL) within Sector1 field - UPDATE Sector2 field with the given 'SectorValue'.

You're making it far too complicated. Do it in two steps:


-- Take care of the ones where Sector1 isn't NULL first, because
-- the second step will make the NULL ones disappear
UPDATE <YourTable> SET Sector1Field = @NewSectorValue WHERE Sector1Field IS NOT NULL AND <Other Conditions>

-- Now do the ones that are NULL
UPDATE <YourTable> SET Sector1Field = SectorValue WHERE Sector1Field IS NULL AND <Other Conditions>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources