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

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.....

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);

:cool:

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.

LKBrwn_DBA'S query shld solve ur problem...
Cheers!

IT WORKS!! THANK YOU BOTH VERY MUCH!!

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.

hmmm!
can u post ur query... and couple of example DATA
Cheers!

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'

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);

No luck. I just ran it again and zero rows were updated.

Oops...gave you the wrong query. Here's the correct one, which is still not updating any rows:

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);


Steve

Hi I am not able to figure out the reason but if ur columns are VARCHAR type, sometime the value may not match. try using TRIM() option.

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

Cheers!