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

[football]

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

[chrisrlong]

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

[chrisrlong]

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

[stecal]

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}]...];

[slimdave]

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
) ;

[marist89]

how about something like:

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)

[chrisrlong]

Originally posted by marist89
how about something like:

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:

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

[chrisrlong]

And this is my 1000th post!!!!!!!!!!

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

- Chris

[football]

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

[chrisrlong]

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