March 23, 2017 at 11:05 am
Hi,
Here is my script, which queries one table and brings back the results fine. The only problem is that the "Department" column are old Department Names
The New Department names are kept in a different Database Table which has these 2 Columns "DeptCode" - (matches Old Department name) and "DeptReportName" (these are the New Department Names)
I want to return the DeptReportName column and replace the existing values in the Old Department column, any ideas how to do this?
I had a go and can get the results from one table, but no idea how to do lookup and replace the Department column with the new Department names...
SELECT UserID, Firstname, Lastname, Department
FROM dbo.User_table
WHERE (RoleGUID = 'D8144444-4444-4444-9444-040DA27C4444')
Any help would be appreciated
Thanks
March 23, 2017 at 11:14 am
If you add CREATE TABLE statements for both tables and some test data, chances of getting help would go up significantly 🙂
March 23, 2017 at 11:40 am
Zidar - Thursday, March 23, 2017 11:14 AMIf you add CREATE TABLE statements for both tables and some test data, chances of getting help would go up significantly 🙂
Not sure how to do that, it's only 4 fields in table 1 and 2 fields in table 2
Here's what it should do:
Lookup using "Old Department" Column in Table 1 and match with "Old Department" column in Table 2 and return "New Department"Column results and overwrite existing value in Old Department Column in Table 1
March 23, 2017 at 1:53 pm
you can do an UPDATE with a JOIN in it like it shows in this link:
https://msdn.microsoft.com/en-us/library/ms177523.aspx#OtherTables
March 24, 2017 at 11:12 am
Hi,
try the code below::
update dbo.User_table
Set Department = case
when y.Department = w.DeptCode then w.DeptReportName
else NULL
END
from dbo.User_table y inner join New_Department w on y.Department=w.Deptcode
Regards
Anna
March 24, 2017 at 2:04 pm
earlysunrirse - Friday, March 24, 2017 11:12 AMHi,
try the code below::update dbo.User_table
Set Department = case
when y.Department = w.DeptCode then w.DeptReportName
else NULL
END
from dbo.User_table y inner join New_Department w on y.Department=w.DeptcodeRegards
Anna
This uses two different instances of dbo.User_Table: one aliased "y" and the other unaliased. This may not (and probably WILL not) produce the results that you are expecting.
Also, the CASE expression is unnecessary in this statement, because the join conditions are exactly the same as the CASE conditions, so only the first condition will ever be met. The CASE expression would still be unnecessary even if you changed the JOIN to a LEFT OUTER JOIN.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 24, 2017 at 8:37 pm
drew.allen - Friday, March 24, 2017 2:04 PMearlysunrirse - Friday, March 24, 2017 11:12 AMHi,
try the code below::update dbo.User_table
Set Department = case
when y.Department = w.DeptCode then w.DeptReportName
else NULL
END
from dbo.User_table y inner join New_Department w on y.Department=w.DeptcodeRegards
AnnaThis uses two different instances of dbo.User_Table: one aliased "y" and the other unaliased. This may not (and probably WILL not) produce the results that you are expecting.
Also, the CASE expression is unnecessary in this statement, because the join conditions are exactly the same as the CASE conditions, so only the first condition will ever be met. The CASE expression would still be unnecessary even if you changed the JOIN to a LEFT OUTER JOIN.
Drew
Drew,
Can you please show me how you would do this? New to SQL, i'm sure this is easy for you guys, but any coded example would be appreciated
March 27, 2017 at 9:31 am
rkelly58 - Friday, March 24, 2017 8:37 PMdrew.allen - Friday, March 24, 2017 2:04 PMearlysunrirse - Friday, March 24, 2017 11:12 AMHi,
try the code below::update dbo.User_table
Set Department = case
when y.Department = w.DeptCode then w.DeptReportName
else NULL
END
from dbo.User_table y inner join New_Department w on y.Department=w.DeptcodeRegards
AnnaThis uses two different instances of dbo.User_Table: one aliased "y" and the other unaliased. This may not (and probably WILL not) produce the results that you are expecting.
Also, the CASE expression is unnecessary in this statement, because the join conditions are exactly the same as the CASE conditions, so only the first condition will ever be met. The CASE expression would still be unnecessary even if you changed the JOIN to a LEFT OUTER JOIN.
Drew
Drew,
Can you please show me how you would do this? New to SQL, i'm sure this is easy for you guys, but any coded example would be appreciated
earlysunrise was mostly right. You just need to make sure that you refer to the table the same way in both the UPDATE clause and the FROM clause.
UPDATE y -- Use alias already established
SET Department = w.DeptReportName -- Get rid of unnecessary CASE expression
FROM dbo.User_table y
INNER JOIN New_Department w
ON y.Department=w.Deptcode
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply