Update Table - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Update Table

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-23 : 09:03:09

declare @cprap table (polnum varchar(8),co_code varchar(2), credit_code int, face_amt int,pay_pend int, pay_due int)
insert @cprap
select 'L1112233', '2', 90, null, null, null union all
select 'L1112233', '2', 90, null, null, null union all
select 'L1113344', '2', 90, null, null, null

declare @actg table (polnum varchar(8),co_code varchar(2),credit_code int, debit_code int, trans_amt int)
insert @actg
select 'L1112233', '2', 38, 530, 100000 union all
select 'L1112233', '2', 38, 110, 550 union all
select 'L1112233', '2', 110, 38, 750 union all
select 'L1112233', '2', 38, 530, 200000 union all
select 'L1113344', '2', 38, 530,500000 union all
select 'L1113344', '2', 38, 110,25 union all
select 'L1113344', '2', 110, 38,125

The values in the @cprap need to be updated as below.

Face_amt is sum of trans_amounts where credit_code = 38 and debit_code = 530.
pay_pend is sum of trans_amounts where credit_code = 38 and debit_code = 110.
pay_due is sum of trans_amounts where credit_code = 110 and debit_code = 38.

Expected Result

select 'L1112233', '2', 90, 300000, 550, 750 union all
select 'L1112233', '2', 90, 300000, 550, 750 union all
select 'L1113344', '2', 90, 500000, 25, 125

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 09:10:15

[code]declare @cprap table (polnum varchar(8),co_code varchar(2), credit_code int, face_amt int,pay_pend int, pay_due int)
insert @cprap
select 'L1112233', '2', 90, null, null, null union all
select 'L1112233', '2', 90, null, null, null union all
select 'L1113344', '2', 90, null, null, null

declare @actg table (polnum varchar(8),co_code varchar(2),credit_code int, debit_code int, trans_amt int)
insert @actg
select 'L1112233', '2', 38, 530, 100000 union all
select 'L1112233', '2', 38, 110, 550 union all
select 'L1112233', '2', 110, 38, 750 union all
select 'L1112233', '2', 38, 530, 200000 union all
select 'L1113344', '2', 38, 530,500000 union all
select 'L1113344', '2', 38, 110,25 union all
select 'L1113344', '2', 110, 38,125

UPDATE x
SET x.Face_amt = y.Face_amt,
x.pay_pend = y.pay_pend,
x.pay_due = y.pay_due
FROM @cprap AS x
INNER JOIN (
SELECT polnum, co_code,
sum(case when credit_code = 38 and debit_code = 530 then trans_amt else 0 end) AS Face_amt,
sum(case when credit_code = 38 and debit_code = 110 then trans_amt else 0 end) AS pay_pend,
sum(case when credit_code = 110 and debit_code = 38 then trans_amt else 0 end) As pay_due
from @actg
group by polnum, co_code
) AS y on y.polnum = x.polnum and y.co_code = x.co_code

SELECT *
FROM @cprap[/code]

E 12°55'05.63"
N 56°04'39.26"

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-23 : 09:18:18

try this



declare @cprap table (polnum varchar(8),co_code varchar(2), credit_code int, face_amt int,pay_pend int, pay_due int)
insert @cprap
select 'L1112233', '2', 90, null, null, null union all
select 'L1112233', '2', 90, null, null, null union all
select 'L1113344', '2', 90, null, null, null

declare @actg table (polnum varchar(8),co_code varchar(2),credit_code int, debit_code int, trans_amt int)
insert @actg
select 'L1112233', '2', 38, 530, 100000 union all
select 'L1112233', '2', 38, 110, 550 union all
select 'L1112233', '2', 110, 38, 750 union all
select 'L1112233', '2', 38, 530, 200000 union all
select 'L1113344', '2', 38, 530,500000 union all
select 'L1113344', '2', 38, 110,25 union all
select 'L1113344', '2', 110, 38,125

UPDATE C
SET C.face_amt = A1.faceamount,
	C.pay_pend = A2.paypend,
	C.pay_due = A3.paydue
FROM @cprap C
INNER JOIN 
	( SELECT polnum,co_code,SUM(trans_amt) as faceamount
	  from @actg 
	  WHERE credit_code = 38 and debit_code = 530
	  GROUP BY polnum,co_code) A1 ON A1.POLNUM = C.POLNUM AND a1.co_code = c.co_code
INNER JOIN 
	( SELECT polnum,co_code,SUM(trans_amt) as paypend
	  from @actg 
	  WHERE credit_code = 38 and debit_code = 110
	  GROUP BY polnum,co_code ) A2 ON A2.POLNUM = C.POLNUM AND a2.co_code = c.co_code
INNER JOIN 
	( SELECT polnum,co_code,SUM(trans_amt) as paydue
	  from @actg 
	  WHERE credit_code = 110 and debit_code = 38
	  GROUP BY polnum,co_code ) A3 ON A3.POLNUM = C.POLNUM AND a3.co_code = c.co_code

SELECT * FROM @cprap

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-23 : 09:56:46

Thanks Peter and raky for your solutions, but this update seems to be taking a lot of time which is why I posted here in the first place.

This is the actual query I tried based on Peter's solution.

UPDATE T1
SET T1.FACE_AMT_STAGE = A.FACE_AMT_STAGE,
T1.ADB_AMT_STAGE = A.ADB_AMT_STAGE,
T1.STATE_TAX_WITHHLD_STAGE = A.STATE_TAX_WITHHLD_STAGE,
T1.INCOME_TAX_WITHHLD_STAGE = A.INCOME_TAX_WITHHLD_STAGE,
T1.PREM_RETURN_STAGE = A.PREM_RETURN_STAGE,
T1.PREM_DUE_STAGE = A.PREM_DUE_STAGE,
T1.INTEREST_AMT_STAGE = A.INTEREST_AMT_STAGE
FROM CPRAPEXT T1
INNER JOIN PPOLC AS POL WITH (NOLOCK) ON POL.POLC_KEY0 = CAST(T1.COMPANY_CODE + CAST(T1.POLICY_NUMBER AS CHAR(12)) AS BINARY(14)) AND POL.CONTRACT_REASON IN ('DA','DC','DT','AD')
INNER JOIN (
SELECT POLICY_NUMBER,COMPANY_CODE,
SUM(CASE WHEN CREDIT_CODE = 38 and DEBIT_CODE = 530 AND BENEFIT_TYPE IN ('OR','BA')THEN TRANS_AMOUNT ELSE 0 END) AS FACE_AMT_STAGE,
SUM(CASE WHEN CREDIT_CODE = 38 and DEBIT_CODE = 530 AND BENEFIT_TYPE = 'SU'THEN TRANS_AMOUNT ELSE 0 END) AS ADB_AMT_STAGE,
SUM(CASE WHEN CREDIT_CODE = 820 and DEBIT_CODE = 38 THEN TRANS_AMOUNT ELSE 0 END) AS INCOME_TAX_WITHHLD_STAGE,
SUM(CASE WHEN CREDIT_CODE = 850 and DEBIT_CODE = 38 THEN TRANS_AMOUNT ELSE 0 END) AS STATE_TAX_WITHHLD_STAGE,
SUM(CASE WHEN CREDIT_CODE = 38 and DEBIT_CODE = 110 THEN TRANS_AMOUNT ELSE 0 END) AS PREM_RETURN_STAGE,
SUM(CASE WHEN CREDIT_CODE = 110 and DEBIT_CODE = 38 THEN TRANS_AMOUNT ELSE 0 END) AS PREM_DUE_STAGE,
SUM(CASE WHEN CREDIT_CODE = 38 and DEBIT_CODE = 640 THEN TRANS_AMOUNT ELSE 0 END) AS INTEREST_AMT_STAGE
FROM PACTG
GROUP BY POLICY_NUMBER,COMPANY_CODE
) AS A ON A.POLICY_NUMBER = T1.POLICY_NUMBER and A.COMPANY_CODE = T1.COMPANY_CODE

The PACTG table has over 10 million records, hence I think, the derived table A with the group by is taking forever to complete.

The CPRAPEXT table however, has only 100 records that need to be updated.

Any way, I can reduce the time on the Update?

P.S : I cancelled the query after 4 minutes...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 10:22:58

How does your indexes look like?
And your revised query has additional filtering not mentioned in first post.
Also 10,000,000 records should have been mentioned.

E 12°55'05.63"
N 56°04'39.26"

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-23 : 10:31:55

quote:
Originally posted by Peso

How does your indexes look like?
And your revised query has additional filtering not mentioned in first post.
Also 10,000,000 records should have been mentioned.

E 12°55'05.63"
N 56°04'39.26"

There is one unique clustered index on PACTG (ACTG_KEY0 ) which is a combination of POLICY_NUMBER,COMPANY_CODE and date and time added.

Something like 02L11122332006010108001323. And there are 5 other non clustered unique indexes , nothing involving the POLICY_NUMBER and COMPANY_CODE.

I apologize. I should have mentioned about the volume earlier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources