-
Hi,
I need to update a table called ' preformat' which has many columns. I need to update only 2 columns, which are iso_ccy_cod and acct_ccy_deci_plac on the basis of the value of a column in another table (currency) , iso_ccy_cod.
If the value in currency.iso_ccy_cod is 'ADP' and it exists in the table Preformat.iso_ccy_cod, I need to put the value of currency.ccy_deci_plac into preformat.ccy_deci_plac.
Also, I need to put the value in the column preformat.acct_ccy_deci_plac if the value in currency.iso_ccy_cod exists in preformat.acct_ccy_cod.
Please help me ASAP.
Thanks.
-
Can somebody please help me?
Thanks.
-
How would the two tables be joined?
-
Lee,
I think the reason no one has answered is because it would be either a VERY complicated piece of SQL or a reasonably complex peice of PL/SQL.
Do you know PL/SQL, if so I can give you pointers as to what you will need to do.
Mike
-
Well, I tried again. I tried to construct a statement given these requirements, but they are insufficient.
"I need to update a table called ' preformat' which has many columns. I need to update only 2 columns, which are iso_ccy_cod and acct_ccy_deci_plac on the basis of the value of a column in another table (currency) , iso_ccy_cod. "
You actually appear to want to update ccy_deci_plac and acct_ccy_deci_plac, If I read the rest of this correctly.
"If the value in currency.iso_ccy_cod is 'ADP' and it exists in the table Preformat.iso_ccy_cod, I need to put the value of currency.ccy_deci_plac into preformat.ccy_deci_plac. "
What does "exists in the table Preformat.iso_ccy_cod" mean? As mentioned, you need to provide some join criteria.
"Also, I need to put the value in the column preformat.acct_ccy_deci_plac if the value in currency.iso_ccy_cod exists in preformat.acct_ccy_cod"
What does "I need to put the value in the column..." mean? What value? What does "exists in preformat.acct_ccy_cod" mean?
Just a suggestion, but you might get better and quicker responses if you provide some pseudo-code. Something to the tune of
For every record in "preformat P" that exists in "criteria C"
(Join on P.PK = C.FK )
If C.iso_ccy_cod = 'ADP' then
Set P.ccy_deci_plac = C.ccy_deci_plac
Else
If C.iso_ccy_cod = P.iso_ccy_cod then
Set P.acct_ccy_deci_plac = C.acct_ccy_deci_plac
This is my best interpretation of your requirements. If they are correct, I'll whip you up some SQL.
- Chris
-
The first part is correct.
Secondly, the column acct_ccy_deci_plac has to be updated with the value in currency.ccy_deci_plac when currency.iso_ccy_cod = preformat.acct_ccy_cod.
I hope that I am clear.
Thanks.
-
Try this SQL
Update preformat fmt
set acct_ccy_deci_plac =
(select ccy_deci_plac from iso_ccy_code iso
where fmt.iso_ccy-code = iso.iso_ccy_code)
where fmt.iso_ccy_code='ADP'