Need to modify the Update statement to include two new tables instead of one table

Question

Need to modify the Update statement to include two new tables instead of one table

saptek9

Ten Centuries

Points: 1155
More actions
August 26, 2018 at 4:25 am

#408309

Currently, in existing system i do have following update statement:
Update ProdStg
set unit_cost=case when ci.totcstperacct<ci.cst1 then ci.chk_amt1
when ci.totcstperacct>=ci.prc2 and ci.totcstperacct<ci.cst2 then ci.chk_amt2
when ci.totcstperacct>ci.prc3 then ci.chk_amt3
from ProdStg ps
inner join cp_inv ci on ps.cus_cd=ci.arid
In new database design they split the cp_inv table(columns:cst1,cst2, cst3,prc1,price2,prc3,chk_amt1,chk_amt2,chk_amt3) into two: cp_chk (cst,prc,chk_amt,seqnum) and cp_seq(seq1, seq2,seq3).
Now i need to change above update statement by combining two new tables cp_chk and cp_seq instead cp_inv. Please help me to do this task.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

saptek9 - Sunday, August 26, 2018 4:25 AM
Currently, in existing system i do have following update statement:
Update ProdStg
set unit_cost=case when ci.totcstperacct<ci.cst1 then ci.chk_amt1
when ci.totcstperacct>=ci.prc2 and ci.totcstperacct<ci.cst2 then ci.chk_amt2
when ci.totcstperacct>ci.prc3 then ci.chk_amt3
from ProdStg ps
inner join cp_inv ci on ps.cus_cd=ci.arid
In new database design they split the cp_inv table(columns:cst1,cst2, cst3,prc1,price2,prc3,chk_amt1,chk_amt2,chk_amt3) into two: cp_chk (cst,prc,chk_amt,seqnum) and cp_seq(seq1, seq2,seq3).
Now i need to change above update statement by combining two new tables cp_chk and cp_seq instead cp_inv. Please help me to do this task.

Please provide DDL, sample data in the form of INSERT statements and desired results.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

saptek9 Ten Centuries Points: 1155 More actions · Answer 2

Need to modify the Update statement to include combination two new fields instead of one field

Currently, in existing system i do have following update statement:

Update ProdStg
set unit_cost=case when ci.totcstperacct<ci.cst1 then ci.chk_amt1
when ci.totcstperacct>=ci.prc2 and ci.totcstperacct<ci.cst2 then ci.chk_amt2
when ci.totcstperacct>ci.prc3 then ci.chk_amt3
from ProdStg ps
inner join cp_inv ci on ps.cus_cd=ci.arid

In new database design they split the cp_inv table(columns:cst1,cst2, cst3,prc1,price2,prc3,chk_amt1,chk_amt2,chk_amt3) into two: cp_chk (cst,prc,chk_amt,seqnum)

Now i need to change above update statement by combining two new fields(cst and seqnum combination or prc and seqnum combination or chk_amt and seqnum combination) instead. Please help me to do this task.

Something like below: i need to write in better way:
Update ProdStg
set unit_cost=case when ci.totcstperacct<(select ci.cst1 from cp_chk chk where ch.seqnum=1) then (select ch.chk_amt1 from cp_chk chk where ch.seqnum=1)
when ci.totcstperacct>=(select ci.prc2 from cp_chk chk where ch.seqnum=2) and ci.totcstperacct<(select ci.cst2 from cp_chk chk where ch.seqnum=2) then (select ch.chk_amt3 from cp_chk chk where ch.seqnum=2)
when ci.totcstperacct>(select ci.prc3 from cp_chk chk where ch.seqnum=3) then (select ch.chk_amt3 from cp_chk chk where ch.seqnum=3)
from ProdStg ps
inner join cp_chk ch on ps.cus_cd=ch.arid

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

saptek9 - Sunday, August 26, 2018 5:32 AM
Need to modify the Update statement to include combination two new fields instead of one field
Currently, in existing system i do have following update statement:
Update ProdStg
set unit_cost=case when ci.totcstperacct<ci.cst1 then ci.chk_amt1
when ci.totcstperacct>=ci.prc2 and ci.totcstperacct<ci.cst2 then ci.chk_amt2
when ci.totcstperacct>ci.prc3 then ci.chk_amt3
from ProdStg ps
inner join cp_inv ci on ps.cus_cd=ci.arid
In new database design they split the cp_inv table(columns:cst1,cst2, cst3,prc1,price2,prc3,chk_amt1,chk_amt2,chk_amt3) into two: cp_chk (cst,prc,chk_amt,seqnum)
Now i need to change above update statement by combining two new fields(cst and seqnum combination or prc and seqnum combination or chk_amt and seqnum combination) instead. Please help me to do this task.
Something like below: i need to write in better way:
Update ProdStg
set unit_cost=case when ci.totcstperacct<(select ci.cst1 from cp_chk chk where ch.seqnum=1) then (select ch.chk_amt1 from cp_chk chk where ch.seqnum=1)
when ci.totcstperacct>=(select ci.prc2 from cp_chk chk where ch.seqnum=2) and ci.totcstperacct<(select ci.cst2 from cp_chk chk where ch.seqnum=2) then (select ch.chk_amt3 from cp_chk chk where ch.seqnum=2)
when ci.totcstperacct>(select ci.prc3 from cp_chk chk where ch.seqnum=3) then (select ch.chk_amt3 from cp_chk chk where ch.seqnum=3)
from ProdStg ps
inner join cp_chk ch on ps.cus_cd=ch.arid

If we cut and paste your code into SSMS, it does not run. That is because we do not have access to your tables, your databases and your environment.
The link in my signature will help you to draft questions in a way that allows others to run your code and therefore to provide working solutions.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

saptek9 Ten Centuries Points: 1155 More actions · Answer 4

Current table cp_inv structure:
ID, cst1, cst2,cst3,prc1,prc2,prc3, chk_amt1, chk_amt2, chk_amt3

New table cp_chk structure:
ID, cst, prc, chk_amt, seq_num
basically 9 columns replaced by 4 columns. Now they removed 1,2,3 and want to bring two column combination cst and seq_num combination for example

Update ProdStg
set unit_cost=case when ci.totcstperacct<ci.cst1 then ci.chk_amt1
when ci.totcstperacct>=ci.prc2 and ci.totcstperacct<ci.cst2 then ci.chk_amt2
when ci.totcstperacct>ci.prc3 then ci.chk_amt3
from ProdStg ps
inner join cp_inv ci on ps.cus_cd=ci.arid

Something like below: i need to write in better way:
Update ProdStg
set unit_cost=case when ci.totcstperacct<(select ci.cst from cp_chk chk where ch.seqnum=1) then (select ch.chk_amt from cp_chk chk where ch.seqnum=1)
when ci.totcstperacct>=(select ci.prc from cp_chk chk where ch.seqnum=2) and ci.totcstperacct<(select ci.cst from cp_chk chk where ch.seqnum=2) then (select ch.chk_amt from cp_chk chk where ch.seqnum=2)
when ci.totcstperacct>(select ci.prc from cp_chk chk where ch.seqnum=3) then (select ch.chk_amt from cp_chk chk where ch.seqnum=3)
from ProdStg ps
inner join cp_chk ch on ps.cus_cd=ch.arid

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 5

saptek9 - Sunday, August 26, 2018 4:25 AM
Currently, in existing system i do have following update statement:
Update ProdStg
set unit_cost=case when ci.totcstperacct<ci.cst1 then ci.chk_amt1
when ci.totcstperacct>=ci.prc2 and ci.totcstperacct<ci.cst2 then ci.chk_amt2
when ci.totcstperacct>ci.prc3 then ci.chk_amt3
from ProdStg ps
inner join cp_inv ci on ps.cus_cd=ci.arid
In new database design they split the cp_inv table(columns:cst1,cst2, cst3,prc1,price2,prc3,chk_amt1,chk_amt2,chk_amt3) into two: cp_chk (cst,prc,chk_amt,seqnum) and cp_seq(seq1, seq2,seq3).
Now i need to change above update statement by combining two new tables cp_chk and cp_seq instead cp_inv. Please help me to do this task.

So do a LEFT JOIN for the two tables to the ProdStg table and make the same type of CASE decisions but using the two tables instead of just the one you currently have.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)