?
Solved

UPDATE DATA IN ONE TABLE FROM ANOTHER USING A SELECT STATEMENT

Posted on 2009-04-22
27
Medium Priority
?
1,104 Views
Last Modified: 2013-12-07
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.

0
Comment
Question by:ewgf2002
  • 8
  • 7
  • 7
  • +1
27 Comments
 
LVL 43

Expert Comment

by:pcelba
ID: 24207982
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24208056
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
 

Author Comment

by:ewgf2002
ID: 24208114
pcelea,  i think the issue is the glaccount is not the same for all rows.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 43

Expert Comment

by:pcelba
ID: 24208194
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24208206
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
 

Author Comment

by:ewgf2002
ID: 24208251
mwvisa1

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

Expert Comment

by:pcelba
ID: 24208282
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24208386
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24208391
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24208401
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24208414
Angel Eyes, does Oracle auto recognize the rownum OR is that the result of an analytical row_number() OVER statement?
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24208474
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
 

Author Comment

by:ewgf2002
ID: 24208581
i am using oracle and i am still getting a syntax error.  The glaccounts are different.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24208613
>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
 

Author Comment

by:ewgf2002
ID: 24208649
I need the glaccount from one table to be inserted into the other table.  There is not just one value for the glaccount.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24208679
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
 

Author Comment

by:ewgf2002
ID: 24208765
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24208771
Thanks!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24208804
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
 
LVL 43

Expert Comment

by:pcelba
ID: 24208854
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
 

Author Comment

by:ewgf2002
ID: 24208917
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24208962
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
 
LVL 43

Expert Comment

by:pcelba
ID: 24208986
I would remove the rownum condition. If it fails again we will know data are still ambiguous for update.
0
 

Author Comment

by:ewgf2002
ID: 24209141
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24209182
>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
 
LVL 43

Expert Comment

by:pcelba
ID: 24209232
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
 

Author Closing Comment

by:ewgf2002
ID: 31573439
Thank you angellll !
I found the other condition that I missed.  Works perfectly.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question