Update a table

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'