Help with Update Query - Multiple Joins

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

SQL Server 2000 Forums

Transact-SQL (2000)

Help with Update Query - Multiple Joins

Author

Topic

boblarson
Starting Member

17 Posts

Posted - 2008-04-30 : 18:45:55

Hello -

I'm still fairly new to the Transact-SQL coding so I've run across one that I've not had to do before and I'm having trouble finding out how to do this one. I can manage an UPDATE query where there is one table that the data is coming from, but how do you do this one:


CREATE PROCEDURE dbo.sp_JT_BB09_CaseLots_SizePcts_MinSizes AS
UPDATE [data extract - buffer buy report]
 
SET [data extract - buffer buy report].XS_Size_Percents = [master - size percents].XS_Size_Percents,
[data extract - buffer buy report].S_Size_Percents = [master - size percents].S_Size_Percents,
[data extract - buffer buy report].M_Size_Percents = [master - size percents].M_Size_Percents,
[data extract - buffer buy report].L_Size_Percents = [master - size percents].L_Size_Percents,
[data extract - buffer buy report].XL_Size_Percents = [master - size percents].XL_Size_Percents,
[data extract - buffer buy report].XXL_Size_Percents = [master - size percents].XXL_Size_Percents,
[data extract - buffer buy report].XXXL_Size_Percents = [master - size percents].XXXL_Size_Percents,
[data extract - buffer buy report].XS_Case_Lot = [master - case lot].XS_Case_Lot,
[data extract - buffer buy report].S_Case_Lot = [master - case lot].S_Case_Lot,
[data extract - buffer buy report].M_Case_Lot = [master - case lot].M_Case_Lot,
[data extract - buffer buy report].L_Case_Lot = [master - case lot].L_Case_Lot,
[data extract - buffer buy report].XL_Case_Lot = [master - case lot].XL_Case_Lot,
[data extract - buffer buy report].XXL_Case_Lot = [master - case lot].XXL_Case_Lot,
[data extract - buffer buy report].XXXL_Case_Lot = [master - case lot].XXXL_Case_Lot,
[data extract - buffer buy report].XS_Min_Buff_Size = [master - min size buffer amt].XS_Min_Buff_Size,
[data extract - buffer buy report].S_Min_Buff_Size = [master - min size buffer amt].S_Min_Buff_Size,
[data extract - buffer buy report].M_Min_Buff_Size = [master - min size buffer amt].M_Min_Buff_Size,
[data extract - buffer buy report].L_Min_Buff_Size = [master - min size buffer amt].L_Min_Buff_Size,
[data extract - buffer buy report].XL_Min_Buff_Size = [master - min size buffer amt].XL_Min_Buff_Size,
[data extract - buffer buy report].XXL_Min_Buff_Size = [master - min size buffer amt].XXL_Min_Buff_Size,
[data extract - buffer buy report].XXXL_Min_Buff_Size = [master - min size buffer amt].XXXL_Min_Buff_Size;
 
FROM What goes here?

And how do the joins work?  This is an Access query that I'm converting 
INNER JOIN ([master - size percents] INNER JOIN ([master - case lot] INNER JOIN [data extract - buffer buy report] ON
([master - case lot].Material = [data extract - buffer buy report].material) AND ([master - case lot].Plant = [data extract - buffer buy report].plant)) ON
([master - size percents].Material = [data extract - buffer buy report].material) AND ([master - size percents].Plant = [data extract - buffer buy report].plant)) ON
([master - min size buffer amt].Material = [data extract - buffer buy report].material) AND ([master - min size buffer amt].Plant = [data extract - buffer buy report].plant)

Thanks,

Bob Larson

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-30 : 18:49:42

Here is an example UPDATE with a join:

UPDATE t
SET SomeColumn = a.A
FROM Table1 t
INNER JOIN Table2 a
ON t.B = a.B

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

boblarson
Starting Member

17 Posts

Posted - 2008-04-30 : 19:06:43

Tara -

Thanks, I'm having trouble with the multiple tables (I'm updating one table with data from [master - size percents], [master - case lot], and [master - min size buffer amt] and trying to get the multiple join correct.

The sample you gave is something I have no trouble with, but I'm not getting something right when I move into the multiple table territory. Even if it is just a generic example, could you do a sample based on your sample but with updating the one table with columns from two or more tables and joins on two or more tables? I can then translate that into my own code. I'm just confused as to the syntax when specifying multiple tables in the join (do I use spaces or commas to separate or do they just run on as in the code in my first post, etc.).

Thanks,

Bob Larson

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-30 : 19:13:13

Sure, here you go:

DATE t
SET SomeColumn = a.A, SomeOtherColumn = b.B, AnotherColumn = c.Z
FROM Table1 t
INNER JOIN Table2 a
ON t.B = a.B
INNER JOIN Table3 b
ON a.B = b.B
INNER JOIN Table4 c
ON t.C = c.C

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources