Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

update from multiple tables Expand / Collapse
Author
Message
Posted Wednesday, October 2, 2013 12:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 9:42 PM
Points: 6, Visits: 61
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
Post #1500658
Posted Wednesday, October 2, 2013 12:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 6,732, Visits: 8,484
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1500665
Posted Wednesday, October 2, 2013 1:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:04 AM
Points: 6,801, Visits: 14,016
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1500680
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse