parthmeister (10/2/2013)
Hi Folks,i am using 4 tables company, users, ARSAP, ARCUS to update a column in company table.
the tsql i am using is below.
UPDATE c
SET c.comp_primaryuserid = u.user_userid
FROM Company c, Users u
LEFT JOIN SAMINC.dbo.ARSAP z on z.NAMEEMPL = 'u.user_firstname u.user_lastname'
LEFT JOIN SAMINC.dbo.ARCUS y on z.CODESLSP = y.CODESLSP1 WHERE y.IDCUST = c.comp_companyid
the validation "LEFT JOIN SAMINC.dbo.ARSAP z on z.NAMEEMPL = 'u.user_firstname u.user_lastname'" is not working though the sql is correct, there is no relation between ARSAP table and users table.
I just want to match the firstname lastname from column NAMEEMPL (table ARSAP) to users table user_firstname user_lastname.
NAMEEMPL is char(60) and have data for e.g. Bill Bhassion
user_firstname nvarchar(20) and user_lastname nvarchar(40) are two different fields for e.g. user_firstname = Bill user_lastname = Bhassion
i am stuck at this point and i cant update c.comp_primaryuserid = u.user_userid to fix this up.
any ideas to help?
regards,
parth
Your joins need a little work - try this:
UPDATE c
SET c.comp_primaryuserid = u.user_userid
FROM Company c
INNER JOIN SAMINC.dbo.ARCUS y
ON y.IDCUST = c.comp_companyid
INNER JOIN SAMINC.dbo.ARSAP z
ON z.CODESLSP = y.CODESLSP1
INNER JOIN Users u
ON z.NAMEEMPL = u.user_firstname + ' ' + u.user_lastname
Before running it as an UPDATE, check the statement as a SELECT using a selection of columns from the tables.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden