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)
 Updating multiple rows from separate table

Author  Topic 

carmitage
Starting Member

8 Posts
Posted - 2008-09-11 : 16:01:20
Basically, I have a horizontal and a vertical table. I want to update the horizontal with the vertical. I have limited this to 3 fields. There are actually about 30 so the simpler the better.

This is what I want to do, but sql doesn't allow multiple fields in the set statement like this:

Update ALM.dbo.QRM_VALUATION_ANALYSIS_test1
Set (DN100_$_DELTA,DN200_$_DELTA,DN300_$_DELTA) =
(select (case VSCENID when 4 then amount else 0 end) DN100_$_DELTA,
(case VSCENID when 5 then amount else 0 end) DN200_$_DELTA,
(case VSCENID when 6 then amount else 0 end) DN300_$_DELTA
from QRM_VALUATION_ANALYSIS_TEST_TEMP
where QRM_VALUATION_ANALYSIS_TEST_TEMP.COMPID = QRM_VALUATION_ANALYSIS_test1.COMPID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.PORTID = QRM_VALUATION_ANALYSIS_test1.PORTID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.PANLYID = QRM_VALUATION_ANALYSIS_test1.PANLYID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.accountid = QRM_VALUATION_ANALYSIS_test1.accountid AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.BCKTID = QRM_VALUATION_ANALYSIS_test1.BCKTID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.CF_TYPE =1 )
where exists (select amount from QRM_VALUATION_ANALYSIS_TEST_TEMP
where QRM_VALUATION_ANALYSIS_TEST_TEMP.COMPID = QRM_VALUATION_ANALYSIS_test1.COMPID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.PORTID = QRM_VALUATION_ANALYSIS_test1.PORTID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.PANLYID = QRM_VALUATION_ANALYSIS_test1.PANLYID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.accountid = QRM_VALUATION_ANALYSIS_test1.accountid AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.BCKTID = QRM_VALUATION_ANALYSIS_test1.BCKTID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.CF_TYPE =1 )

Currently, I have this update for each field:

Update ALM.dbo.QRM_VALUATION_ANALYSIS_test1
Set UP100_$_DELTA = (select amount
from QRM_VALUATION_ANALYSIS_TEST_TEMP
where QRM_VALUATION_ANALYSIS_TEST_TEMP.COMPID = QRM_VALUATION_ANALYSIS_test1.COMPID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.PORTID = QRM_VALUATION_ANALYSIS_test1.PORTID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.PANLYID = QRM_VALUATION_ANALYSIS_test1.PANLYID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.accountid = QRM_VALUATION_ANALYSIS_test1.accountid AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.BCKTID = QRM_VALUATION_ANALYSIS_test1.BCKTID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.CF_TYPE =1 AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.VSCENID = 1)
where exists (select amount
from QRM_VALUATION_ANALYSIS_TEST_TEMP
where QRM_VALUATION_ANALYSIS_TEST_TEMP.COMPID = QRM_VALUATION_ANALYSIS_test1.COMPID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.PORTID = QRM_VALUATION_ANALYSIS_test1.PORTID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.PANLYID = QRM_VALUATION_ANALYSIS_test1.PANLYID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.accountid = QRM_VALUATION_ANALYSIS_test1.accountid AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.BCKTID = QRM_VALUATION_ANALYSIS_test1.BCKTID AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.CF_TYPE =1 AND
QRM_VALUATION_ANALYSIS_TEST_TEMP.VSCENID = 1)

It works, but it takes a very long time. Any ideas how to make this work better and more simply?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2008-09-11 : 18:22:46
Your explanation is not extremly clear, but let me know if this is what you are looking for.


Update a
set 
	 a.UP100_$_DELTA = case when b.VSCENID = 4 then b.Amount else a.UP100_$_DELTA end
	,a.UP200_$_DELTA = case when b.VSCENID = 5 then b.Amount else a.UP200_$_DELTA end
	,a.UP300_$_DELTA = case when b.VSCENID = 6 then b.Amount else a.UP300_$_DELTA end
from
	ALM.dbo.QRM_VALUATION_ANALYSIS_test1 a
Inner Join
	QRM_VALUATION_ANALYSIS_TEST_TEMP b
on		a.CompID = b.CompID
and		a.PortID = b.PortID
and		a.PANLYID = b.PanlyID
and		a.accountId = b.AccountID
and		a.BcktID = b.bcktID
and		b.CF_TYPE = 1 -- Not sure if this item is related to the column
--and	b.VSCENID = 1 --U have the #1 on your query, but in your mock case statement you have 4 = UP100??



Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

carmitage
Starting Member

8 Posts
Posted - 2008-09-16 : 11:44:55
Yes, thank you. I appreciate your help.

It partially works. Running this...

Update a
set
a.UP100_$_DELTA = case when b.VSCENID = 4 then b.Amount end
,a.UP200_$_DELTA = case when b.VSCENID = 5 then b.Amount end
,a.UP300_$_DELTA = case when b.VSCENID = 6 then b.Amount end
from
ALM.dbo.QRM_VALUATION_ANALYSIS_test a
Inner Join
ALM.dbo.QRM_VALUATION_ANALYSIS_TEST_TEMP b
on a.CompID = b.CompID
and a.PortID = b.PortID
and a.PANLYID = b.PanlyID
and a.accountId = b.AccountID
and a.BcktID = b.bcktID
and b.CF_TYPE = 1


It updates UP200_$_DELTA, but not the other two. I have turned the update into a select statement so the from is exactly the same and there is an amount for VSCENID = 4 and 6. It doesn't appear to be the data. If I comment out the two other case statements it doesn't update either. Any ideas?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-16 : 11:49:38
just give some sample data to illustrate your scenario and then explain output you want
Go to Top of Page

carmitage
Starting Member

8 Posts
Posted - 2008-09-16 : 14:40:28
SELECT [COMPID]
,[PORTID]
,[MRKTID]
,[ASMPID]
,[CYCLID]
,[ACCOUNTID]
,[BCKTID]
,[UP100_$_DELTA]
,[UP200_$_DELTA]
,[UP300_$_DELTA]
FROM [ALM].[dbo].[QRM_VALUATION_ANALYSIS_test]


SELECT [COMPID]
,[PORTID]
,[MRKTID]
,[ASMPID]
,[CYCLID]
,[PANLYID]
,[ACCOUNTID]
,[BCKTID]
,[CF_TYPE]
,[VSCENID]
,[AMOUNT]
FROM [ALM].[dbo].[QRM_VALUATION_ANALYSIS_TEST_TEMP]

I want to update UP100_$_DELTA, UP200_$_DELTA, UP300_$_DELTA in the QRM_VALUATION_ANALYSIS_TEST table with the amount from the QRM_VALUATION_ANALYSIS_TEST_TEMP table. If QRM_VALUATION_ANALYSIS_TEST_TEMP.vscenid = 4, I want to update QRM_VALUATION_ANALYSIS_TEST.UP100_$_DELTA field with QRM_VALUATION_ANALYSIS_TEST_TEMP.amount.If QRM_VALUATION_ANALYSIS_TEST_TEMP.vscenid = 5, I want to update QRM_VALUATION_ANALYSIS_TEST.UP200_$_DELTA field with QRM_VALUATION_ANALYSIS_TEST_TEMP.amount. If QRM_VALUATION_ANALYSIS_TEST_TEMP.vscenid = 6, I want to update QRM_VALUATION_ANALYSIS_TEST.UP300_$_DELTA field with QRM_VALUATION_ANALYSIS_TEST_TEMP.amount.

So QRM_VALUATION_ANALYSIS_TEST_TEMP looks like:
Accountid vscenid amount
1 4 10.00
1 5 25.00
1 6 45.00


I want QRM_VALUATION_ANALYSIS_TEST to look like:
Accountid UP100_$_DELTA UP200_$_DELTA UP300_$_DELTA
1 10.00 25.00 45.00
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-17 : 03:20:34
[code]UPDATE t1
SET t1.[UP100_$_DELTA]=t2.[AMOUNT4],
t1.[UP200_$_DELTA]=t2.[AMOUNT5],
t1.[UP300_$_DELTA]=t2.[AMOUNT6]
FROM [ALM].[dbo].[QRM_VALUATION_ANALYSIS_test] t1
INNER JOIN
(
SELECT [PORTID]
,[MRKTID]
,[ASMPID]
,[CYCLID]
,[ACCOUNTID]
,[BCKTID],
,MAX(CASE WHEN [VSCENID]=4 THEN [AMOUNT] ELSE NUL END) AS AMOUNT4
,MAX(CASE WHEN [VSCENID]=5 THEN [AMOUNT] ELSE NUL END) AS AMOUNT5
,MAX(CASE WHEN [VSCENID]=6 THEN [AMOUNT] ELSE NUL END) AS AMOUNT6
FROM [ALM].[dbo].[QRM_VALUATION_ANALYSIS_TEST_TEMP]
GROUP BY [PORTID]
,[MRKTID]
,[ASMPID]
,[CYCLID]
,[ACCOUNTID]
,[BCKTID]) t2
ON t2.[PORTID]=t1.[PORTID]
AND t2.[MRKTID]=t1.[MRKTID]
AND t2.[ASMPID]=t1.[ASMPID]
AND t2.[CYCLID]=t1.[CYCLID]
AND t2.[ACCOUNTID]=t1.[ACCOUNTID]
AND t2.[BCKTID]=t1.[BCKTID][/code]
Go to Top of Page

carmitage
Starting Member

8 Posts
Posted - 2008-09-17 : 15:07:36
Thank you very much. That's actually quite similar to what I was able to come up with.


Update main_table
set
main_table.UP100_$_DELTA = delta_u100,
main_table.UP200_$_DELTA = delta_u200,
main_table.UP300_$_DELTA = delta_u300
from
ALM.dbo.QRM_VALUATION_ANALYSIS_test main_table
Join
(select COMPID,PORTID,PANLYID , accountid ,
BCKTID,
sum(case VSCENID when 1 then amount else 0 end) delta_u100,
sum(case VSCENID when 2 then amount else 0 end) delta_u200,
sum(case VSCENID when 3 then amount else 0 end) delta_u300
from ALM.dbo.QRM_VALUATION_ANALYSIS_TEST_TEMP temp_table
where temp_table.CF_TYPE =1
group by COMPID,PORTID,PANLYID , accountid ,
BCKTID) select_data
on main_table.CompID = select_data.CompID
and main_table.PortID = select_data.PortID
and main_table.PANLYID = select_data.PanlyID
and main_table.accountId = select_data.AccountID
and main_table.BcktID = select_data.bcktID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-18 : 00:36:48
You're welcome
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site