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

mskittenAsked:
Who is Participating?
 
garysadlerConnect With a Mentor Commented:
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
 
gatorvipConnect With a Mentor Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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