ORACLE SQL UPDATE issue

Question

I am trying to run an update query.
Table PS_Z_TREND_NOW_TBL contains columns DESCR254 and URLS.
I am using PSPRSMDEFN which has many columns including PORTAL_LABEL and URLS. I want to run the below update query to update the PS_Z_TREND_NOW_TBL.URLS column where the values match for PS_Z_TREND_NOW_TBL.DESCR254 and PSPRSMDEFN.URLS. What do I need to change/add in the query below to make this work? When I try to run the below query it gives an error stating, 'single row subquery returns more than one row'

UPDATE PS_Z_TREND_NOW_TBL now
SET now.URLS = t.URLS
WHERE now.DESCR254 IN(
select t.PORTAL_URLTEXT from PSPRSMDEFN t, PS_Z_TREND_NOW_TBL trd
where t.VERSION = 
(select MIN(t2.VERSION) 
    from PSPRSMDEFN t2
    WHERE t2.PORTAL_LABEL = trd.DESCR254 
    AND t2.PORTAL_REFTYPE = 'C'
    group by t2.PORTAL_LABEL
)
AND t.PORTAL_LABEL = trd.DESCR254 
    AND t.PORTAL_REFTYPE = 'C'
    and t.PORTAL_NAME = 'EMPLOYEE'
)

This part of the query from above returns multiple rows of data. The values it returns are the same values in PS_Z_TREND_NOW_TBL.DESCR254. I want to match these values up with what is returned in the query to update the URLS field.

select t.PORTAL_URLTEXT from PSPRSMDEFN t, PS_Z_TREND_NOW_TBL trd
    where t.VERSION = 
    (select MIN(t2.VERSION) 
        from PSPRSMDEFN t2
        WHERE t2.PORTAL_LABEL = trd.DESCR254 
        AND t2.PORTAL_REFTYPE = 'C'
        group by t2.PORTAL_LABEL
    )
    AND t.PORTAL_LABEL = trd.DESCR254 
        AND t.PORTAL_REFTYPE = 'C'
        and t.PORTAL_NAME = 'EMPLOYEE'

Isn't the problem self explainatory? When doing an update, you can only set a rows value to a single value, and not several values returned by a query. You'll need to create logic to make the bottom select statement return a single row instead...your issue is really with your data and nothing in your code — Twelfth
– Twelfth, Commented Sep 26, 2014 at 18:18
It's helpful, you're just not understanding well. You are setting URLS = select statement that returns t.PORTAL_URLTEXT. It's returning multiple values for t.PORTAL_URLTEXT with the filters you have and you cannot update a single field to be multiple rows. Look at the results from the query that is returning multiple rows, determine which one of those t.PORTAL_URLTEXT you want to set URLS to, and come up with rules to select only that one line. If it's returning the same value multiple times, using distinct would work as well. Once again, nothing wrong with the code of your query — Twelfth
– Twelfth, Commented Sep 26, 2014 at 18:29
If you want more help than that...run the query that is giving multiple rows and edit your question to include those results...we can go from there — Twelfth
– Twelfth, Commented Sep 26, 2014 at 18:31

Aramillo · Accepted Answer · 2014-09-26 18:55:08Z

1

I think this part of your query is that return multiple rows:

t.VERSION = 
(select MIN(t2.VERSION) 
    from PSPRSMDEFN t2
    WHERE t2.PORTAL_LABEL = trd.DESCR254 
    AND t2.PORTAL_REFTYPE = 'C'
    group by t2.PORTAL_LABEL
)

Because when you group by and use min, it returns all min values for each group, that's why i think you should use in instead =

t.VERSION in 
    (select MIN(t2.VERSION) 
        from PSPRSMDEFN t2
        WHERE t2.PORTAL_LABEL = trd.DESCR254 
        AND t2.PORTAL_REFTYPE = 'C'
        group by t2.PORTAL_LABEL
    )

answered Sep 26, 2014 at 18:55

Aramillo

3,2263 gold badges27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3586248 · Accepted Answer · 2014-09-26 20:00:50Z

Below is the answer. I used an EXISTS clause:

UPDATE PS_Z_TREND_NOW_TBL now
SET URLS =

(select t.PORTAL_URLTEXT from PSPRSMDEFN t, PS_Z_TREND_NOW_TBL trd
where t.VERSION = 
(select MIN(t2.VERSION) 
    from PSPRSMDEFN t2
    WHERE t2.PORTAL_LABEL = trd.DESCR254 
    AND t2.PORTAL_REFTYPE = 'C'
    group by t2.PORTAL_LABEL
)
AND t.PORTAL_LABEL = trd.DESCR254 
    AND t.PORTAL_REFTYPE = 'C'
    and t.PORTAL_NAME = 'EMPLOYEE'
    AND NOW.DESCR254 = T.PORTAL_LABEL
)

where Exists 
(select t.PORTAL_URLTEXT from PSPRSMDEFN t, PS_Z_TREND_NOW_TBL trd
where t.VERSION =
(select MIN(t2.VERSION) 
    from PSPRSMDEFN t2
    WHERE t2.PORTAL_LABEL = trd.DESCR254 
    AND t2.PORTAL_REFTYPE = 'C'
    group by t2.PORTAL_LABEL
)
AND t.PORTAL_LABEL = trd.DESCR254 
    AND t.PORTAL_REFTYPE = 'C'
    and t.PORTAL_NAME = 'EMPLOYEE'
    AND NOW.DESCR254 = T.PORTAL_LABEL
)

Collectives™ on Stack Overflow

ORACLE SQL UPDATE issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related