DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Update a table

  1. #1
    Join Date
    Feb 2000
    Posts
    142
    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.

  2. #2
    Join Date
    Feb 2000
    Posts
    142
    Can somebody please help me?

    Thanks.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    How would the two tables be joined?
    Jeff Hunter

  4. #4
    Join Date
    Oct 2000
    Posts
    90
    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

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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 <or whatever>)
    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

  6. #6
    Join Date
    Feb 2000
    Posts
    142
    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.

  7. #7
    Join Date
    Dec 2000
    Posts
    126
    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'






Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width