UPDATE DATA IN ONE TABLE FROM ANOTHER USING A SELECT STATEMENT

I am not PL/SQL expert but seems you have several rows with same ITEMNUM in inventory table. Is it right? If yes and GLACCOUNT is same for all these rows, this could work:

update invcost a
set a.glaccount = (select TOP 1 b.GLACCOUNT from inventory b
                 where a.ITEMNUM = b.ITEMNUM
                 and b.GLACCOUNT is not null)
where exists
                 (select 1 from inventory b
                 where a.ITEMNUM = b.ITEMNUM
                 and b.GLACCOUNT is not null )
and a.glaccount is null

Seems like you need an inner join.  I am not a PL/SQL expert either, but syntax wise it would look something like this.

UPDATE a
SET a.glaccount = b.GLACCOUNT
FROM invcost a
INNER JOIN inventory b ON a.ITEMNUM = b.ITEMNUM AND b.GLACCOUNT is not null
WHERE a.glaccount is null;

pcelea,  i think the issue is the glaccount is not the same for all rows.

So you should test it because if it is not same then you don't know how to update missing values. If the following query returns some rows then you have to clarify what to update:

select ITEMNUM, COUNT(GLACCOUNT) as glcnt
 from inventory b
where GLACCOUNT is not null
GROUP BY ITEMNUM
HAVING COUNT(GLACCOUNT) > 1

Did you try my query yet?  That will match on the ITEMNUM from the inventory table which has GLACCOUNT you want and update invcost table accordingly.

mwvisa1

I got a syntax error when trying to run your query.

The syntax should be:

UPDATE invcost
SET glaccount = b.GLACCOUNT
FROM invcost a
INNER JOIN inventory b ON a.ITEMNUM = b.ITEMNUM AND b.GLACCOUNT is not null
WHERE a.glaccount is null;

to get rid of the error:

update invcost a
set a.glaccount = (select b.GLACCOUNT from nventory b
                 where a.ITEMNUM = b.ITEMNUM
                 and b.GLACCOUNT is not null
                 and rownum = 1)
where exists
                 (select 1 from inventory b
                 where a.ITEMNUM = b.ITEMNUM
                 and b.GLACCOUNT is not null )
and a.glaccount is null

Select allOpen in new window

Try this:
http://www.orafaq.com/forum/?t=msg&th=47970/0/

UPDATE 
(SELECT a.glaccount AS oldAccount
, b.glaccount AS newAccount
FROM invcost a
INNER JOIN inventory b ON a.ITEMNUM = b.ITEMNUM AND b.GLACCOUNT is not null
WHERE a.glaccount is null)
SET oldAccount = newAccount;

Select allOpen in new window

remarks: the TOP 1  or the UPDATE ... JOIN won't work in oracle...

now, if the original query returns that error, it means that for a single ITEMNUM, the query returns several rows.
from there, assuming you get different values of GLACCOUNT, which one should the subquery use for the update?

Angel Eyes, does Oracle auto recognize the rownum OR is that the result of an analytical row_number() OVER statement?

Thanks for TOP n and rownum clarification (I am more FoxPro than Oracle) but I would say to reduce select output to one row does not solve the problem in the case there are ambigous assignments of GL account to Item Numbers.

If they need just SOME value assigned then it is OK, of course.

i am using oracle and i am still getting a syntax error.  The glaccounts are different.

>Angel Eyes, does Oracle auto recognize the rownum OR is that the result of an analytical row_number() OVER statement?

oracle has a ROWNUM  syntax (it's not really a function)

>Thanks for TOP n and rownum clarification (I am more FoxPro than Oracle) but I would say to reduce select output to one row does not solve the problem in the case there are ambigous assignments of GL account to Item Numbers.

it does get rid of the runtime error, but as you say correctly, it does not really solve the problem.


>i am using oracle and i am still getting a syntax error.  The glaccounts are different.
then, please, clarify, what value of glaccount should be chosen.
which rule should be applied?

I need the glaccount from one table to be inserted into the other table.  There is not just one value for the glaccount.

ewgf2002,

  please try to be more collaborative.
  we  did understand that you have different value of glaccount, but, the question is:
  for a single value of ITEMNUM, should it return different value of glaccount also?

  if yes, which 1 (one) of those values should be taken for the update, again, per value of ITEMNUM.

  if you don't understand that question, please post sample data of the 2 tables BEFORE and AFTER the update as you need it to happen.

angellll,
the answer to your question is yes.  the value that i will need will be from the inventory table.

Example

Inventory table:
itemnum                 glaccount
123                        44444
456                        55555
789                        777777

Current Invost table:
itemnum                 glaccount
123                        
456                        
789                        

Invcost table needs to be updated with the following from the Inventory table:
123                        44444
456                        55555
789                        777777

Thanks!

if the data is like that, your UPDATE should NOT give you that error.
you must have something like


Inventory table:
itemnum                 glaccount
123                        44444
456                        55555
456                        55555
789                        777777

or

123                        44444
456                        55555
456                        11111
789                        777777


in case 1, my addition of AND ROWNUM = 1 will solve both the error and the problem, as all value of glaccount for itemnum = 456 are effectively the same.

in case 2, you have 2 values: 111111 and 55555 for the same itemnum 456.
and here is where you have a problem: which of the 2 values to choose, and why?

Did you try this test?

select ITEMNUM, COUNT(GLACCOUNT) as glcnt
 from inventory b
where GLACCOUNT is not null
GROUP BY ITEMNUM
HAVING COUNT(GLACCOUNT) > 1

angellll,

i fall under the case two scenario.  There are the same item numbers but with different glaccounts.

Example:

Inventory table:
itemnum                 glaccount         location
123                        44444                KY
456                        55555                NC
456                        11111                KY
789                        777777              CA

Current Invost table:
itemnum                 glaccount         location
123                                                  KY
456                                                  NC
456                                                  KY
789                                                  CA
                   

Invcost table needs to be updated with the following from the Inventory table:
itemnum                 glaccount         location
123                        44444                KY
456                        55555                NC
456                        11111                KY
789                        777777              CA

I would remove the rownum condition. If it fails again we will know data are still ambiguous for update.

pcelba:
I removed the rownum = 1 and the error reappeared.  With the rownum = 1 I am not updating all the records that need to be updated.


update maximo.invcost a
set a.glaccount = (select b.GLACCOUNT from maximo.inventory b
                 where a.ITEMNUM = b.ITEMNUM
                 and a.siteid = b.siteid
                 and b.GLACCOUNT is not null
                 and a.glaccount is null)
where exists
                 (select 1 from maximo.inventory b
                 where a.ITEMNUM = b.ITEMNUM
                 and a.siteid = b.siteid
                 and b.GLACCOUNT is not null
                 and a.glaccount is null )
and a.glaccount is null

>I removed the rownum = 1 and the error reappeared
means, you still have the same issue, that you need more conditions...
so, please use the same method I indicated: if you have 1 value for itemnum and location, could there be multiple rows/different values for glaccount. what additional column is there to be added to the join

It means there is some additional dependency in your tables or rules for GL account assignment are still unknown for us.

What returns this select:

select ITEMNUM, siteid, COUNT(GLACCOUNT) as glcnt
 from inventory b
where GLACCOUNT is not null
GROUP BY ITEMNUM, siteid
HAVING COUNT(GLACCOUNT) > 1

Thank you angellll !
I found the other condition that I missed.  Works perfectly.