August 5, 2009 at 9:55 am
Hello,
I am struggling with this SQL code. I use Oracle SQL Developer and it compiles fine without any errors.
When I run it in my client application I get this: ORA-01427: single-row subquery returns more than one row
UPDATE USERS
SET (Firstname,Lastname) = (
SELECT a.Firstname,a.Lastname FROM USERS
INNER JOIN (
SELECT Carname, username,
CASE WHEN instr(',', CarValue) > 0 THEN LTRIM(RTRIM(SUBSTR(CarValue, instr(',', CarValue) - 1))) ELSE LTRIM(RTRIM(SUBSTR(CarValue, instr(' ', CarValue) + 1, LENGTH(CarValue) - instr(' ', CarValue) + 1))) END as Lastname,
CASE WHEN instr(',', CarValue) > 0 THEN LTRIM(RTRIM(SUBSTR(CarValue, instr(',', CarValue) + 1, LENGTH(CarValue) - instr(',', CarValue) + 1))) ELSE LTRIM(RTRIM(SUBSTR(CarValue, instr(' ', CarValue) - 1))) END as Firstname
FROM ADS_Properties
INNER JOIN ADS_users ON userID = CarUserID
WHERE UserName IN (SELECT UserName FROM USERS)
AND CarName = 'displayName' AND (CarValue LIKE '%,%' OR CarValue LIKE '% %')
) a ON a.UserName = USERS.UserName
WHERE (NULLIF(USERS.FirstName, ' ') IS NULL OR NULLIF(Users.lastname, ' ') IS NULL)) , Nothing)
Does someone know how I can optimize my code?
August 5, 2009 at 11:53 am
This is not an optimization issue.
Inline view a.k.a. subquery is returning multiple values, update statement cannot update a single row with multiple values then it fails.
For update statement to succeed inline view has to return a single value; check predicate and make it unique.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 20, 2009 at 2:51 pm
me also use it and its really good enough.......
Join Jenny Craig [/url]
Jenny craig
August 23, 2009 at 5:49 pm
useracc2096 (8/20/2009)
me also use it and its really good enough.......
:w00t: is this plain spam?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply