Update ... order by .. error :PLS-00103
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Update ... order by .. error :PLS-00103

  1. #1
    Join Date
    Oct 2002
    Posts
    10

    Red face Update ... order by .. error :PLS-00103

    Hi,

    I have a table 'A'. It has fields (tablename, group, performed).

    This table has 10000 rows. To begin with, 'perfmomed' field is null for all the records. Typical group values are 001, 002, 003,.... 050. Every group has the same set of tables.

    I need to update 60 rows of this table every day and change the performed field's value to true. The trick is I need to update the records in group order not table order or not randomly. Meaning on first day I need to change 60 records from group 001 and next day, if there any records remaing in 001 and records from grop 002 etc...

    I issue :

    update A set PERFORMED = 'TRUE'
    where PERFORMED is null
    and rownum < 61 ;

    This works fine, except, the 60 records are picked in random not by group.

    So I changed the query to

    update A set PERFORMED = 'TRUE'
    where PERFORMED is null
    and rownum < 61
    order by group, tablename ;

    This statement gives error PLS-00103

    Can any only tell me how I can achive the update in a sequecnce instead of randomly.

    Can any one give me a hint how to solve this...

    Thanks,
    -Football

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Interesting problem.

    Extremely odd requirement, but and interesting problem.

    I'm not sure you can do this in straight SQL. I'll keep pondering on it, however.

    For PL/SQL, you could use the BULK cursor operations to collect the 60 records you want to update, then use the FORALL to update them all in one shot. That would be the next-fastest thing to a single SQL statement.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    BTW, I get an oracle error: ORA-01732: data manipulation operation not legal on this view

    What is the PL/SQL error that you're getting (as I'm too lazy to go look it up )

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Hint #1: You can't use "order by" in your update statement the way it is being used now.

    SELECT
    order_by_clause
    Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

    Syntax for UPDATE is:

    update_statement ::=
    UPDATE {table_reference | [THE] (subquery1)} [alias]
    SET
    {column_name = {sql_expression | (subquery2)}
    | (column_name [, column_name]...) = (subquery3)}
    [, {column_name = {sql_expression | (subquery2)}
    | (column_name [, column_name]...) = (subquery3)}]...
    [WHERE {search_condition | CURRENT_OF cursor_name}] [RETURNING]
    [row_expression [, row_expression]... INTO
    {variable_name | :host_variable_name}
    [, {variable_name | :host_variable_name}]...];
    Last edited by stecal; 10-30-2002 at 05:13 PM.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Does this work ...

    update A set PERFORMED = 'TRUE'
    where rowid in
    (
    select rowid from
    (
    select rowid from A
    where PERFORMED is null
    order by group, tablename
    )
    where rownum < 61
    ) ;

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    how about something like:
    Code:
    UPDATE a 
       SET myField = 'TRUE'
    WHERE myKeyVal in (
       SELECT myKeyVal 
       FROM a 
       WHERE myGroup = 'xxx' 
       AND rownum < 61 
       ORDER BY whatever_you_want_to_order_by)
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by marist89
    how about something like:
    Code:
    UPDATE a 
       SET myField = 'TRUE'
    WHERE myKeyVal in (
       SELECT myKeyVal 
       FROM a 
       WHERE myGroup = 'xxx' 
       AND rownum < 61 
       ORDER BY whatever_you_want_to_order_by)
    Doh!

    Of course

    (hangs head in shame)

    But with one slight modification:

    Code:
    UPDATE 
       CRL
    SET 
       COL1 = SYSDATE
    WHERE
       COL2 IN
          (
          SELECT 
             COL2 
          FROM
             (
             SELECT 
                COL2 
             FROM 
                CRL 
             ORDER BY 
                COL2
             )
          WHERE 
             ROWNUM <= 60
          )
    You need to do the WHERE ROWNUM one level out from the ORDER BY

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    And this is my 1000th post!!!!!!!!!!

    (okay, so Jeff is un-impressed, but it made me happy )

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    Oct 2002
    Posts
    10

    Thank you alll.....

    Hi Guys,

    Thanks a lot for your replies. Especially to 'Chrisrlong' for moderating and 'stecal' to pointing me that order by cannot be used directly with update.

    SLIMDAVE's solution perfectly suites my requirement. Thanks SLIMDAVE.

    Mirsat89 solution is fine if there is a 'key' field in the table. But unfortunately my table doesnít have a key field.

    I made some changes to my update statement based on SLIMDAVE's template and ran the update from SQLPLUS. It works's fine. Let me put this into my full application and see how it works.

    I am really surprised by your overwhelming response.

    Thanks for your help....!!!!!

    -Football

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339

    Re: Thank you alll.....

    Originally posted by football
    Mirsat89 solution is fine if there is a 'key' field in the table. But unfortunately my table doesnít have a key field.
    Uh, you may want to do something about that. [B]Every[B] table needs a PK. If you don't have a 'natural' candidate key then make a surrogate one (actually, should always use surrogate, IMHO, but that's for another time).

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

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


Click Here to Expand Forum to Full Width