update statement sql server 2005

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 2005 Forums

Transact-SQL (2005)

update statement sql server 2005

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-23 : 11:18:29

Hi,

I have a temp table with a columns such as bu_id,salesrep_id,billto_id

I have 3 other lookup tables:
businessunit table with bu_id,bufile_id
salesrep table with salesrep_id, salesrep_fileid
billto table with billto_id and billtofile_id

The values that are inputted into the temp table are the fileids. I need to upldate the values with the ids. There is no relationship btw the 3 lookup tables.
My question is if in one update statement i can update the 3 different fields in the temp table?
Currently i have 3 differenct updates an example:

UPDATE TEMP_DDPL2
SET TEMP_DDPL2.businessunit_id = BusinessUnit.businessunit_id
FROM TEMP_DDPL2 AS Temp_DDPL2
INNER JOIN  dbo.BusinessUnit AS BusinessUnit 
ON BusinessUnit.businessunit_fileID = Temp_DDPL2.businessunit_id

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-23 : 11:43:32

Can either of the 3 ids be null in the temp table?
can you show us some sample data?

quote:
Originally posted by collie

Hi,

I have a temp table with a columns such as bu_id,salesrep_id,billto_id

I have 3 other lookup tables:
businessunit table with bu_id,bufile_id
salesrep table with salesrep_id, salesrep_fileid
billto table with billto_id and billtofile_id

The values that are inputted into the temp table are the fileids. I need to upldate the values with the ids. There is no relationship btw the 3 lookup tables.
My question is if in one update statement i can update the 3 different fields in the temp table?
Currently i have 3 differenct updates an example:
UPDATE TEMP_DDPL2
SET TEMP_DDPL2.businessunit_id = BusinessUnit.businessunit_id
FROM TEMP_DDPL2 AS Temp_DDPL2
INNER JOIN dbo.BusinessUnit AS BusinessUnit
ON BusinessUnit.businessunit_fileID = Temp_DDPL2.businessunit_id

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-24 : 12:53:05

Hi,

the ids can be null in the temp table

Temp table
bu_id,salesrep_id,billto_id
B11 S1235 B4343
B12 S4534 B3433
B11 S43455 B6432
B114 S2563 B0997
businessunit table
bu_id,bufile_id
1 B11
2 B12
3 B13
4 B14

salesrep table
salesrep_id, salesrep_fileid
1 S1235
2 S4534
3 S43455
3 S2563

billto table
billto_id and billtofile_id
1 B4343
2 B3433
3 B6432
4 B0997

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 13:28:30

[code]UPDATE t
SET t.bu_id = COALESCE(bu.bu_id,t.bu_id),
t.salesrep_id = COALESCE(sr.salesrep_id,t.salesrep_id),
t.billto_id = COALESCE(bt.billto_id,t.billto_id)
FROM temp t
LEFT JOIN businessunit bu
ON bu.bu_id = t.bu_id
LEFT JOIN salesrep sr
ON sr.salesrep_fileid = t.salesrep_id
LEFT JOIN billto bt
ON bt.billtofile_id = t.billto_id
WHERE bu.bu_id IS NOT NULL
OR sr.salesrep_id IS NOT NULL
OR bt.billto_id IS NOT NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources