DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Urgent help needed!!

  1. #1
    Join Date
    Aug 2003
    Posts
    100

    Urgent help needed!!

    I'm trying to write an Oracle procedure to do the following and I'm having some trouble. Here's the problem:

    I have a table that contains an item number and a location number. I want to take each of the item numbers and look in another item cross reference table to see if it exists. If so, I want to update the original item number with the new one. I need to do this same thing for the location numbers, which have another separate cross reference table. If either the item or location numbers do not exist in the cross reference tables, update a column in the original table with a status of FAIL, or something like that.

    If anyone can give me some advice, I'd be forever grateful.

    Steve

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    I have a table that contains an item number and a location number. I want to take each of the item numbers and look in another item cross reference table to see if it exists. If so, I want to update the original item number with the new one.

    "If so, I want to update the original item number with the new one." In which feild???

    can u be a bit clear.....
    Cheers!
    OraKid.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Red face

    Try something like this:

    update A_Table u
    set item_nbr = (
    select new_item_nbr from item_xref x
    where x.item_nbr = u.item_nbr)
    where exists (
    select 1 from item_xref x
    where x.item_nbr = u.item_nbr);

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Aug 2003
    Posts
    100
    Balajiyes, I'd like to either update the original item number in the source table, or if that's not possible, update a blank column on that same table.

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    LKBrwn_DBA'S query shld solve ur problem...
    Cheers!

  6. #6
    Join Date
    Aug 2003
    Posts
    100
    IT WORKS!! THANK YOU BOTH VERY MUCH!!

  7. #7
    Join Date
    Aug 2003
    Posts
    100
    This is odd...I ran this query against a table I had created to hold the xref numbers, and it worked great. Now, I point it to a real xref table and it doesn't update anything, even though both the xref table and the source table have an item called 'XYZ' for example.

  8. #8
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hmmm!
    can u post ur query... and couple of example DATA
    Cheers!
    Cheers!
    OraKid.

  9. #9
    Join Date
    Aug 2003
    Posts
    100
    Here's the query...

    update temp_schedrcpts u
    set manu_item = (select imlitm from F4101 x, temp_schedrcpts u
    where x.imaitm = u.legacy_item)
    where exists (select 1 from F4101 x
    where x.imaitm = u.legacy_item);


    NOTE: the temp_schedrcpts table has a row where the legacy_item = 'XYZ' and the F4101 table has a row where imaitm = 'XYZ'

  10. #10
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    try this...
    update temp_schedrcpts u
    set manu_item = (select imlitm from F4101 x
    where x.imaitm = u.legacy_item)
    where exists (select 1 from F4101 x
    where x.imaitm = u.legacy_item);
    Cheers!
    OraKid.

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