Post reply

Oracle PL\SQL for a T-SQL Developer

  • August 5, 2011 at 11:38 am

    #255322

    I know this is a really stupid question (given what I saw on the Oracle boards) but

    I am trying to help someone in my company who is using a tool called SQL Developer and it uses PL\SQL. I am having trouble understanding the syntax (since I can't test it and am totally unfamiliar with PL\SQL) used when you are trying to update multiple rows with a subquery. We came up with this update statement

    UPDATE CLEAN_DATA_0500 CLN

    SET CLN.HIST_INSTANCE (

    SELECT HIT.HIST_INSTANCE

    FROM HIST_INSTANCE_TBL HIT

    WHERE CLN.MIN_SERVICE_FROM_DATE = HIT.DATE_1)

    but it returns the Oracle error message

    ORA-01427: single-row subquery returns more than one row

    I googled this but most of the responses I found on the Oracle boards were "Your subquery is returning more than one row - fix the problem." I couldn't find anyone who gave the OP an answer on how to fix the problem. I am hoping that someone here can help.

    Thanks so much,

    Sue B

  • August 5, 2011 at 12:21 pm

    #1364580

    it's not really a dumb question, but one that you will end up tripping over more than once.

    when you do an update like this:

    UPDATE SomeColumn = (SELECT....)

    the SELECT statement has to return one row (per row being updated)

    if it returns more than one row, the engine would not know which row to assign out of the multiple rows returned, so you get that error.

    usually you need to refine the query a little more...you could change it to SELECT TOP 1....in TSQL or add for oracle add WHERE rownumber = 1,

    or clean up the where statement with tigher join criteria

    [/code]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 5, 2011 at 12:31 pm

    #1364590

    Lowell,

    Thanks for the quick response. Just to make sure I understand, the rownumber=1 would go in the where clause like so

    UPDATE CLEAN_DATA_0500 CLN

    SET CLN.HIST_INSTANCE (

    SELECT HIT.HIST_INSTANCE

    FROM HIST_INSTANCE_TBL HIT

    WHERE CLN.MIN_SERVICE_FROM_DATE = HIT.DATE_1 and rownumber=1)

    Will this result in all the rows which meet the criteria being updated or only the first row ?

    Sorry to be so dense but this syntax is killing me - especially without access to test it so I can figure it out.

    Thanks again,

    Sue B

  • August 6, 2011 at 12:25 pm

    #1364800

    I would say subquery has a problem, forcing the query to update using the first row returned may be just hidding a bigger issue.

    Be sure subquery's predicate is good enough to return a single row - that's the way it should be.

    Just for future reference, SQL Developer is nothing but a GUI tool and has nothing to do with the issue or the resolution of the issue. Language is PL/SQL (actualy PL stands for Programming Language) and it works the same way no matter if you execute it using SQLPLUS, SQL Developer, Toad, DBArtisan or whatever other tool you have at hand.

    _____________________________________
    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 6, 2011 at 7:35 pm

    #1364841

    It would also help to see the ddl for the two tables and some sample data, and expected results based on the sample data.

  • August 8, 2011 at 4:53 am

    #1365088

    Thanks for the information guys. I don't have access to the SQL Developer tool. I use SSMS and T-SQL so am having are hard time tyring to help this person in another city wiht PL/SQL. I'll see if I can get the DDL, insert statements and expected results. From what you are saying, we need to narrow the subquery to only return a single row for updating - which I get. I'm thinking some sort of max or min statement.

    Thanks again for the help.

    Sue B

  • August 8, 2011 at 5:24 am

    #1365099

    Oracle SQL Developer is a free download straight from the oracle web site.....but i think they require you to register:

    http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 8, 2011 at 5:48 am

    #1365114

    Lowell,

    Thanks for the info. I'll download it and see if I can connect to our Oracle db. From what I've read, once I set the subquery to return a single row, I have to use a cursor to update each of the rows ? Is this correct ?

    Thanks,

    Sue B

  • August 8, 2011 at 5:51 am

    #1365117

    sbelche-760308 (8/8/2011)

    Lowell,

    Thanks for the info. I'll download it and see if I can connect to our Oracle db. From what I've read, once I set the subquery to return a single row, I have to use a cursor to update each of the rows ? Is this correct ?

    No. Subquery will be executed once per each updateable row therefore no explicit cursor is needed; this is a text book set based operation, don't try to make it a row-by-row one.

    _____________________________________
    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 8, 2011 at 5:53 am

    #1365119

    Paul,

    Thanks for the correction. I have never had to write a cursor so was hoping my assumption was incorrect.

    Thanks,

    Sue B

  • August 8, 2011 at 9:18 pm

    #1365615

    You might want to try querying to work out which rows are giving you duplicates. Something like the latter should work (based on the code you supplied):

    selectHIT.DATE_1,

    count(*)

    fromCLEAN_DATA_0500 CLN

    inner join

    HIST_INSTANCE_TBL HIT

    onCLN.MIN_SERVICE_FROM_DATE = HIT.DATE_1

    group by

    HIT.DATE_1

    havingcount(*) > 1;

    That should show the dates that are doubling up. To resolve it, it may be something as simple as introducing a DISTINCT or better join criteria, but it's hard to know without seeing the data.

    sbelche-760308 (8/5/2011)

    Just to make sure I understand, the rownumber=1 would go in the where clause like so

    I think you'd want "ROWNUM" not "rownumber". Others have already pointed out the issues with this approach.

    Out of interest, I use the free SQLTools[/url] as an Oracle query tool:

  • August 9, 2011 at 4:54 am

    #1365747

    Bruce,

    Thanks for the information. I know what is causing the problem - it's a many to many relationship so the subquery returns more than one row. I think I have it figured out. Unfortunately, the person I am trying to help, won't do anything to aid the effort. I'll give him my last suggestion and then he is on his own.

    I appreciate everyone helping me even though I wasn't able to provide much information.

    Thanks again,

    Sue B

  • August 11, 2011 at 5:32 pm

    #1367664

    Did you solve your problem? Usually what I do when this happens to me is I change the "update" to a "select" so I can see why I'm getting more than one row back.

    For example,

    I change

    update x

    set a = (select b from y where x.i = y.j)

    to

    select *

    from x

    where a in (select b from y where x.i = y.j)

    Hope I understand your issue correctly if you haven't solved already.

    Clay

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply