Problem using Case wtih Update

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

Transact-SQL (2000)

Problem using Case wtih Update

Author

Topic

danielc
Starting Member

49 Posts

Posted - 2006-08-09 : 11:50:56

Hello folks,

I'm having a heck of a time figuring the following problem:

In my database I have two tables (tbl1 and tbl2). I would like to update feild [ln] in tbl1 with data from feild G1FirstName in tbl2. When I use the following query I get the following error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'G1FirstName'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'G1FirstName'.

UPDATE dbo..tbl1.stu
SET dbo.tbl1.ln =
CASE
WHEN dbo.tbl2.G1FirstName <> ''
THEN dbo.tbl2.G1FirstName
ELSE Null
END

Your help is greatly appreciated.

Thank you.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-09 : 11:57:47

Your syntax is a little off. Do something like the following (NULLIF can replace the CASE statement), but you need to include tbl2 in the query which means you need to join it to tbl1 (on some field that you don't specify)

UPDATE t1
SET t1.ln = NULLIF(t2.G1FirstName, '')
FROM dbo.tbl1 AS t1
JOIN dbo.tbl2 AS t2
ON ???

Mark

danielc
Starting Member

49 Posts

Posted - 2006-08-09 : 14:05:55

Something i just realized is that if it doesn't meet the criteria then I don't want it to do anything. In other words don't update that column. When using the below query:

UPDATE dbo.stu
SET dbo.stu.ln =
CASE
WHEN G1FirstName <> '' AND G1LastName <> ''
THEN G1FirstName + ' ' + G1LastName
END
FROM STU JOIN StudentEnrollment ON stu.studentid = StudentEnrollment.studentid

It updates the column dbo.stu.ln with NULL. How can I avoid this?

Thank you,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-09 : 14:12:20

[code]
UPDATE s
SET ln =
CASE
WHEN G1FirstName <> '' AND G1LastName <> '' THEN G1FirstName + ' ' + G1LastName
ELSE s.ln
END
FROM STU s
INNER JOIN StudentEnrollment se
ON s.studentid = se.studentid
[/code]

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources