[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2102
  • Last Modified:

SQL Update multiple rows from one table to another: ORA-01427

Hello,

I'm trying to copy data from one table to another. However, I can't seem to get the code right.
The error I'm getting is ORA-01427: single-row subquery returns more than one row.

I would like to copy the date information from svjob.dttmstart to svjob.dttmconstruct where svjob.typ1 is like %onst%, however, I also need to ensure that the svjob is linked to the correctsite (svsiteheader). The unique site id is idrec. When I run the SELECT statement by itself, it returns 1053 rows.

Any help would be appreciated.

Thanks!


UPDATE svconstruct  
SET dttmconstruct = (SELECT svjob.dttmstart
                                   FROM ((svsiteheader LEFT JOIN svconstruct ON svsiteheader.idsite = svconstruct.idsite)
                                   JOIN svjob ON svsiteheader.idsite = svjob.idsite)                                                                   
                     WHERE (lower(svjob.typ1) LIKE '%onst%')
                     AND svjob.dttmstart IS NOT NULL), sysmoduser = 'svconstruct_dates', sysmoddate = sysdate
                     
WHERE EXISTS (SELECT svjob.dttmstart
                     FROM ((svsiteheader LEFT JOIN svconstruct ON svsiteheader.idsite = svconstruct.idsite)
                                   JOIN svjob ON svsiteheader.idsite = svjob.idsite)                                                                   
                     WHERE (lower(svjob.typ1) LIKE '%onst%')
                     AND svjob.dttmstart IS NOT NULL);

Open in new window

0
mskitten
Asked:
mskitten
  • 4
  • 2
2 Solutions
 
garysadlerCommented:
You want to correlate the subquery with the parent query rather than including the svconstruct table in the subquery as well.  Try this:

UPDATE svconstruct  
SET dttmconstruct = (SELECT svjob.dttmstart
                     FROM svsiteheader JOIN svjob ON svsiteheader.idsite = svjob.idsite
                     WHERE svsiteheader.idsite = svconstruct.idsite
                       AND (lower(svjob.typ1) LIKE '%onst%')
                       AND svjob.dttmstart IS NOT NULL),
  sysmoduser = 'svconstruct_dates', sysmoddate = sysdate
WHERE EXISTS (SELECT 1
              FROM svsiteheader JOIN svjob ON svsiteheader.idsite = svjob.idsite
              WHERE svsiteheader.idsite = svconstruct.idsite
                AND (lower(svjob.typ1) LIKE '%onst%')
                AND svjob.dttmstart IS NOT NULL)

If that doesn't work, it might help to present more infomation about the tables in question, such as primary keys.
0
 
mskittenAuthor Commented:
Hi garysadler,

I am still getting the ORA-01427 error.
The Primary Key for each table is idrec.
0
 
gatorvipCommented:
What that means is that you have more than 1 row returning from the "SELECT svjob.dttmstart" , so Oracle doesn't know which one of those to assign to dttmconstruct. You'll have to decide on which criteria to use to assign the date.

You can try something like this and see if it fixes your problem: SELECT distinct svjob.dttmstart
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mskittenAuthor Commented:
Ok, let me do a quick cleanup. I think I see where the issue is. I'll let you know if it works.
0
 
mskittenAuthor Commented:
Thanks for your help!
0
 
mskittenAuthor Commented:
After cleaning duplicate svjob.typ1's, the script works great! Thanks!
0
 
garysadlerCommented:
Glad to help.  It may be worth your while to add another unique constraint on the svjob table so that duplicate entries are not possible, or including the DISTINCT function as gatorvip suggested.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now