UPDATE DATA IN ONE TABLE FROM ANOTHER USING A SELECT STATEMENT

I am trying to use the following code to update data from one table and put that data from the select statement into another table.  The code is as follows:

update invcost a
set a.glaccount = (select b.GLACCOUNT from nventory 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

THE ISSUE IS I AM GETTING ORA-01427: single-row subquery returns more than one row.

I am assuming this is because the values in the table from the select has more than one value.  For example:  The glaccount field in inventory is either, 1234, 5678, 9101, 3214.

I need to update the fields in invcost to the same values in inventory.

ewgf2002Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
so, you need an additional condition, using location field!!!
update invcost a
set a.glaccount = (select b.GLACCOUNT from nventory b
                 where a.ITEMNUM = b.ITEMNUM
                 and a.location = b.location
                 and b.GLACCOUNT is not null
                 and rownum = 1)
where exists
                 (select 1 from inventory b
                 where a.ITEMNUM = b.ITEMNUM
                 and a.location = b.location
                 and b.GLACCOUNT is not null )
and a.glaccount is null

Open in new window

0
 
pcelbaCommented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
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;
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
ewgf2002Author Commented:
pcelea,  i think the issue is the glaccount is not the same for all rows.
0
 
pcelbaCommented:
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

0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
ewgf2002Author Commented:
mwvisa1

I got a syntax error when trying to run your query.
0
 
pcelbaCommented:
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;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
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;

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
 
Kevin CrossChief Technology OfficerCommented:
Angel Eyes, does Oracle auto recognize the rownum OR is that the result of an analytical row_number() OVER statement?
0
 
pcelbaCommented:
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.
0
 
ewgf2002Author Commented:
i am using oracle and i am still getting a syntax error.  The glaccounts are different.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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?
0
 
ewgf2002Author Commented:
I need the glaccount from one table to be inserted into the other table.  There is not just one value for the glaccount.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
 
ewgf2002Author Commented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
Thanks!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?

0
 
pcelbaCommented:
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
0
 
ewgf2002Author Commented:
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
0
 
pcelbaCommented:
I would remove the rownum condition. If it fails again we will know data are still ambiguous for update.
0
 
ewgf2002Author Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
0
 
pcelbaCommented:
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
0
 
ewgf2002Author Commented:
Thank you angellll !
I found the other condition that I missed.  Works perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.