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

Other Forums

MS Access

Update Query

Author

Topic

anishap
Yak Posting Veteran

61 Posts

Posted - 2009-05-11 : 17:53:28

I have 3 update queries and was trying to combine using case statement but it is giving me syntax error.

UPDATE hepps SET client = "EMPLOYEE", company = "UW";

UPDATE hepps SET company = "HMC"
WHERE (((homedept) Like "31*"));

UPDATE hepps SET company = "UWMC"
WHERE (((homedept) Like "08*"));

UPDATE HEPPS
SET client = "EMPLOYEE",company = CASE
WHEN homedept like "31*"
THEN "HMC"
WHEN homedept like "08*"
THEN "UWMC"
ELSE "UW"
END
can anyone help me with this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-12 : 01:40:19

quote:
Originally posted by anishap

I have 3 update queries and was trying to combine using case statement but it is giving me syntax error.

UPDATE hepps SET client = "EMPLOYEE", company = "UW";

UPDATE hepps SET company = "HMC"
WHERE (((homedept) Like "31*"));

UPDATE hepps SET company = "UWMC"
WHERE (((homedept) Like "08*"));

UPDATE HEPPS
SET client = "EMPLOYEE",company = CASE
WHEN homedept like "31*"
THEN "HMC"
WHEN homedept like "08*"
THEN "UWMC"
ELSE "UW"
END
can anyone help me with this?

What is the error you are getting?

Madhivanan

Failing to plan is Planning to fail

anishap
Yak Posting Veteran

61 Posts

Posted - 2009-05-12 : 11:17:13

I'm getting syntax error.

Syntax error (missing operator) in query expression.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-05-12 : 16:59:48

Off the top of my head, I'd say it's the double-quotes. Replace them with single quotes. Plus you don't have wildcards in the like statements, they basically are reverting to an equal. Post the exact error message you are receiving.

Terry

-- Procrastinate now!

anishap
Yak Posting Veteran

61 Posts

Posted - 2009-05-13 : 16:00:38

See the below error. It is giving the same error whether it is double quotes or single or without wild cards.

Syntax error (missing operator) in query expression 'CASE
WHEN homedept like '31'
THEN 'HMC'
WHEN homedept like '08'
THEN 'UWMC'
ELSE 'UW'
END'

anishap
Yak Posting Veteran

61 Posts

Posted - 2009-05-15 : 11:30:56

Chaitanya,

Thanks, It worked.

It worked finally using IIF.

company = IIf([homedept] Like "31*","HMC",IIf([homedept] Like "08*","UWMC","UW"));

Thanks for all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources