November 19, 2008 at 10:26 am
Using SQL 2000
Novice to intermediate with T-SQL Query writing
Cleaning up someone elses mess.
The menace (as we loving refer to him as) set up a database for his custom app and pulled the employees over, but he did not use the empl_ID field (primary key). Why? Because he is so smart. Just ask him. He'll tell you all about it.
He wanted a few DTS packages to do some routine things - reasonable. I went back and modified his user table to include the empl_ID. I did some testing on a clone and I finally got it working the way it should. However, the menace asked me to hold off on the DTS for a while. As it turned out, that was a good thing.
He set up his user table using a field called s_name which at the time was equal to last_first_name in the other database. Since then, the original database that holds the employee info has been updated to include a middle initial. Now the last_first_name is different.
orig = Doe, John
now = Doe, John E.
Now he wants the DTS packages, and thankfully, I ran them on the clone and discovered the issue. I cannot pull in or update employees until I fix the entries; otherwise, I will end up with both John Doe's.
I tried setting up an UPDATE to fix this but I am running into an issue because I am trying to use a concatination. Hopefully my story above explains why. Perhaps one of you has a better way to approach this. Here is a copy of the query and error.
UPDATE [StarcalClone].[StarCal].[tbl_user]
SET [starcalclone].[StarCal].[tbl_user].[s_name] = CP.[last_first_name]
FROM [SQL2].[deltekcp].[DELTEK].[empl] AS CP INNER JOIN
[StarcalClone].[StarCal].[tbl_user] as SC
ON (CP.last_name + ", " + CP.first_name) = SC.s_name
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name ', '.
thanks
November 19, 2008 at 11:03 am
You were actually really close. The error likely comes from the double quotes instead of single quotes. What does this give you?
--UPDATE [StarcalClone].[StarCal].[tbl_user]
SELECT [starcalclone].[StarCal].[tbl_user].[s_name], CP.[last_first_name], *
--SET [starcalclone].[StarCal].[tbl_user].[s_name] = CP.[last_first_name]
FROM [SQL2].[deltekcp].[DELTEK].[empl] AS CP
INNER JOIN [StarcalClone].[StarCal].[tbl_user] as SC
ON (CP.last_name + ', ' + CP.first_name) = SC.s_name
If it looks right, your update statement would be:
UPDATE [StarcalClone].[StarCal].[tbl_user]
SET [starcalclone].[StarCal].[tbl_user].[s_name] = CP.[last_first_name]
FROM [SQL2].[deltekcp].[DELTEK].[empl] AS CP
INNER JOIN [StarcalClone].[StarCal].[tbl_user] as SC
ON (CP.last_name + ', ' + CP.first_name) = SC.s_name
November 19, 2008 at 11:11 am
Ah - Syntax, syntax, syntax.
That did it.
Thank you so much
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply