build a new sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

build a new sql query

Author

Topic

vinius
Starting Member

4 Posts

Posted - 2007-04-10 : 14:08:08

Here is my problem.

I need to build a new SQL query and im almost new with all the sql language. Im searching a lot and I have 2 book but I cant found what I'm looking for.

I need to update the Site and Department in the table 1 with the site/department in the table 3 using the table 2 as a join. The table one is an item owned by the person in the table 3 and I need to set the Site and Department as the same.

Table 1

Asset Name | InstanceID | Site | Department

Table 2

InstanceID | User Name |

Table 3

User Name | Site | Department

Help will be really appreciated!

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-10 : 14:13:20

Update T1
SET T1.Site = T3.Site
, T1.Department = T3.Department
FROM Table2 T2
JOIN Table3 T3 ON T2.[Username] = T3.[UserName]

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

vinius
Starting Member

4 Posts

Posted - 2007-04-10 : 14:23:21

Im not sure with the last part of the query.

FROM Table2 T2
JOIN Table3 T3

On the table 1 its not user name but ASSET name...

quote:
Originally posted by dinakar

Update T1
SET T1.Site = T3.Site
, T1.Department = T3.Department
FROM Table2 T2
JOIN Table3 T3 ON T2.[Username] = T3.[UserName]

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

vinius
Starting Member

4 Posts

Posted - 2007-04-10 : 14:47:42

And the InstanceID?

It didn't work... as is..

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-10 : 14:52:34

Untested, but I think this will work:

UPDATE 
	T1
SET 
	T1.Site = T3.Site,
	T1.Department = T3.Department
FROM
	Table1 T1
INNER JOIN 
	Table2 T2
	ON T1.InstanceID = T2.InstanceID 
INNER JOIN
	Table3 T3 
	ON T2.UserName = T3.UserName

-Ryan

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-10 : 14:54:45

quote:
Originally posted by vinius
I need to update the Site and Department in the table 1 with the site/department in the table 3 using the table 2 as a join.

The query I provided is doing just what you asked.

quote:
Originally posted by vinius
Im not sure with the last part of the query.

FROM Table2 T2
JOIN Table3 T3

From the DDL you posted, the only common column between tables 2 and 3 was the [UserName]. If there are other columns or if this [User Name] column is not the right column to be joined, you should mention it in your post.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

vinius
Starting Member

4 Posts

Posted - 2007-04-10 : 15:06:53

Thanks Lamprey!

It work wonderfully!!!

quote:

Originally posted by Lamprey

Untested, but I think this will work:

UPDATE 
	T1
SET 
	T1.Site = T3.Site,
	T1.Department = T3.Department
FROM
	Table1 T1
INNER JOIN 
	Table2 T2
	ON T1.InstanceID = T2.InstanceID 
INNER JOIN
	Table3 T3 
	ON T2.UserName = T3.UserName

-Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources