Results 1 to 7 of 7

Thread: update table

  1. #1
    Join Date
    Oct 2003
    Location
    ny
    Posts
    23

    Red face Unanswered: update table

    hello I'm ne to oracle and I have to update a table that have a dup values

    I have a field upc and for this upc exists two ids
    how can i update this thable

    i tried to do this but it only updated data that has one upc to one id

    UPDATE B_UPC EU SET (EU.ID) =
    (SELECT A.ID FROM A_UPC A
    WHERE A.UPC= EU.UPC and A.TYPE = ''5'')
    WHERE EU.UPC IN (SELECT A.UPC A
    FROM A_UPC A );


    Thank you.

  2. #2
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: update table

    Originally posted by oranewbee
    hello I'm ne to oracle and I have to update a table that have a dup values

    I have a field upc and for this upc exists two ids
    how can i update this thable

    i tried to do this but it only updated data that has one upc to one id

    UPDATE B_UPC EU SET (EU.ID) =
    (SELECT A.ID FROM A_UPC A
    WHERE A.UPC= EU.UPC and A.TYPE = ''5'')
    WHERE EU.UPC IN (SELECT A.UPC A
    FROM A_UPC A );


    Thank you.
    what do you want to do if you have multiple id for one upc?

  3. #3
    Join Date
    Oct 2003
    Location
    ny
    Posts
    23

    Re: update table

    Originally posted by lynden.zhang
    what do you want to do if you have multiple id for one upc?

    I have to do nothing, i just have to show that there are data exists for particular UPC, even if there are two values


    thank you

  4. #4
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: update table

    Originally posted by oranewbee
    I have to do nothing, i just have to show that there are data exists for particular UPC, even if there are two values


    thank you
    what do you want to update the id field to? first id found? or something else?

  5. #5
    Join Date
    Oct 2003
    Location
    ny
    Posts
    23

    Re: update table

    Originally posted by lynden.zhang
    what do you want to update the id field to? first id found? or something else?

    this id is empty since update did not take place yet, when upc found
    i have to insert this ids for particular upc, maybe i have to even recreate a tale to have two ids shown since there might be two for one upc


    i have a table a_upc
    id upc
    1 111
    2 222
    2 211
    3 3333
    3 3311
    4 4444


    i have my table b_upc
    upc id
    111
    222
    333

    if a_upc = b_upc i have to update id in b_upc to that id from a_upc


    Thank u

  6. #6
    Join Date
    Oct 2003
    Location
    ny
    Posts
    23

    Re: update table

    Originally posted by oranewbee
    this id is empty since update did not take place yet, when upc found
    i have to insert this ids for particular upc, maybe i have to even recreate a tale to have two ids shown since there might be two for one upc


    i have a table a_upc
    id upc
    1 111
    2 222
    2 211
    3 3333
    3 3311
    4 4444


    i have my table b_upc
    upc id
    111
    222
    333

    if a_upc = b_upc i have to update id in b_upc to that id from a_upc


    Thank u

    I misstyped

    the upc in a_ups might not be necessary the same for one ups of 111 could be id 1 and 2 and so on


    thank u

  7. #7
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: update table

    Originally posted by oranewbee
    I misstyped

    the upc in a_ups might not be necessary the same for one ups of 111 could be id 1 and 2 and so on


    thank u
    if you want to update the id to the first one found, try

    UPDATE B_UPC EU SET (EU.ID) =
    (SELECT A.ID FROM A_UPC A
    WHERE A.UPC= EU.UPC and A.TYPE = ''5''
    and rownum = 1)
    WHERE EU.UPC IN (SELECT A.UPC A
    FROM A_UPC A );

    or you can concatenate all ids matching upc code '1,2,3' and put it into
    eu.id field, you can try something called stragg. Seach this site for stragg you will find some info about this method.

    I dont see a reason to create another table. A view will be sufficient, if you want to do that way.

Posting Permissions

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