October 2, 2013 at 12:18 am
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
October 2, 2013 at 12:42 am
did you try:
select c.*, u.*
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 u.user_firstname + ' ' + u.user_lastname = z.NAMEEMPL
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 2, 2013 at 1:46 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply