Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2012
    Posts
    8

    Unanswered: Help with Update Statement Please?

    Hello!

    I could use some help writing the following MS SQL Server update statement in Oracle SQL from a generous soul. Please don't judge my crode too harshly, it was written quickly, but it works in SQL Server. I'm trying to assign new sequence numbers to the rows in the contract table because rows were deleted through the application leaving big gaps between sequence numbers. These gaps are causing issues in the functionality of the application (I did not write the application...):

    UPDATE ContractTable
    SET SeqenceNo = TBL.NEW_SEQ
    FROM (SELECT t1.ContractID,t1.SequenceNo, COUNT(t1.SequenceNo) AS NEW_SEQ
    FROM ContractTable t1 INNER JOIN ContractTable t2
    ON t1.ContractID = t2.ContractID
    AND t1.SequenceNo > = t2.SequenceNo
    AND t1.ContractID = '0000000000000000000000027'
    GROUP BY t1.ContractID,t1.SequenceNo) TBL
    WHERE ContractTable.ContractID = TBL.ContractID
    AND ContractTable.SequenceNo = TBL.SequenceNo

    Any assistance with the equivalent Oracle syntax would be greatly appreciated.

    Thanks!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Something like this:
    Code:
    MERGE INTO contractTable ct
    using (
      SELECT t1.rowid,
             row_number() over (order by t1.sequenceNo) as new_seq
      FROM ContractTable t1 
      WHERE t1.ContractID = '0000000000000000000000027'
    ) t ON (ct.rowid = t.rowid)
    WHEN MATCHED THEN 
      UPDATE SET sequenceNo = t.new_seq;
    It's essentially using a windowing function to create a new sequence number on the fly (no need for a self-join here) and then assign that new sequence number to the matching rows of the contractTable.

    But honestly: get your application right, quickly. Any application that depens on "IDs" being gapless is seriously broken (unless there is a legal requirement for gapless Ids)

    And please use [code] tags the next time to make your SQL more readable.
    More details regarding the code tags are here: http://www.dbforums.com/misc.php?do=bbcode
    (That link is at the bottom of each page)

  3. #3
    Join Date
    Mar 2012
    Posts
    8
    Thank you for the suggestion. I'm getting the error that there is a missing keyword when I try this. I've gone out to look at other documentation about your suggested syntax and it looks correct...any thought about the missing keyword thing? Also, in SQL Server the data returned by my select looks something like:

    HTML Code:
    ContractID   SequenceNO   NEW_SEQ
    00044                10                1
    00044                50                2
    00044                99                3
    Will your code basically update the SequenceNO with sequential values like NEW_SEQ?

    Thanks again, I really appreciate it.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >UPDATE ContractTable
    >SET SeqenceNo = TBL.NEW_SEQ
    >FROM (SELECT t1.ContL

    I thought that SQL was a standardized language.
    Please post URL to document that shows UPDATE statement is valid to contain FROM keyword
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2012
    Posts
    8

  6. #6
    Join Date
    Mar 2012
    Posts
    8
    anacedent - My sql works fine in SQL Server. I apologize if it offends, I'm trying to find a solution that works in Oracle. Suggestions are welcomed.

    Thanks.
    Last edited by CPIETRUS; 03-09-12 at 22:24. Reason: clarification

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CPIETRUS View Post
    Thank you for the suggestion. I'm getting the error that there is a missing keyword when I try this.
    Maybe some copy & paste problem? Can you show the exact statement you executed? (maybe a copy & paste of the complete text in the SQL*Plus commandline) I ran it locally and it worked.
    Which Oracle Version are you using?

    Also, in SQL Server the data returned by my select looks something like:

    HTML Code:
    ContractID   SequenceNO   NEW_SEQ
    00044                10                1
    00044                50                2
    00044                99                3
    Will your code basically update the SequenceNO with sequential values like NEW_SEQ?
    Yes. And my understanding is, that this is what you original statement does as well.
    Last edited by shammat; 03-10-12 at 04:57.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    I thought that SQL was a standardized language.
    Please post URL to document that shows UPDATE statement is valid to contain FROM keyword
    FROM is allowed in an UPDATE by PostgreSQL, SQL Server, MySQL and other DBMS.
    As this syntax is not standardized, it's always a hassle to port these statements to other DBMS (not even the three mentioned have the same syntax for this).

  9. #9
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Red face

    For some reason, some people on this forum don't seem to be offering much help. In SQL Server, an update statement can use a FROM in order to update one table from results from one or more other tables.

    I really love ORACLE as a database, but I wish they had taken SQL Servers approach on this one.

    I don't have a database connection at the moment, but the equivalent statement should look like this:
    Code:
    UPDATE ContractTable t
       SET t.SeqenceNo = (SELECT COUNT(t1.SequenceNo)
                            FROM ContractTable t1, 
                                 ContractTable t2
                           WHERE t1.ContractID = t2.ContractID
                             AND t1.SequenceNo >= t2.SequenceNo
                             AND t1.ContractID = '0000000000000000000000027'
    							    AND t1.ContractID = t.ContractID
    							    AND t1.SequenceNo = t.SequenceNo
                        GROUP BY t1.ContractID, t1.SequenceNo)
    This is the basic structure and will probably need some adjustment to your needs.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dayneo View Post
    For some reason, some people on this forum don't seem to be offering much help
    Well, my MERGE statement works at least on my computer.
    As long as CPIETRUS doesn't show us what exactly he/she ran it's hard to tell what failed.

  11. #11
    Join Date
    Mar 2012
    Posts
    8
    Thanks for sticking up for me you other SQL Server users...

    Shammat - thank you for your quick feedback. I ran what you posted. I just copied and pasted it. I'll try it again...maybe I have a strange character in there somewhere or something. I'm on Oracle 9.

    Dayneo - I'll try your suggestion as well.

    Thanks again for the quick and helpful feedback.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CPIETRUS View Post
    I'm on Oracle 9.
    I assumed you were using a supported version of Oracle.

    Although I would assume that statement should run under Oracle 9 it is not surprising that things have changed in the last 10 years.

    But again: without showing exactly what you did (by copy & pasting the SQL*Plus session) there is hardly anything I can do.

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >For some reason, some people on this forum don't seem to be offering much help
    I could not differentiate a posted correct solution from an incorrect solution,
    because I have NO idea what is the expected/desired results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    I could not differentiate a posted correct solution from an incorrect solution,
    because I have NO idea what is the expected/desired results.
    You complained about the fact that there is no FROM in an UPDATE statement even though CPIETRUS clearly stated that the shown statement was for SQL Server (which does support a join in an update statement).

  15. #15
    Join Date
    Mar 2012
    Posts
    8
    Hi Shammat,

    I tried your sql in an Oracle 10g environment and it works. I just need to stage some test data to make sure the results are what I was trying to achieve. Unfortuantely, I don't always have control over what level of database version I have to work with... Sometimes it is what it is...

    Thanks again for your quick and helpful response.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •