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

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

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 :D)

- Chris

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

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

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)

---

Quote:

---

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:D

(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

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

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

- Chris

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

Quote:

---

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

Quote:

---

Originally posted by chrisrlong
Everytable needs a PK.

---

To every rule there is an exception, and here it is -- it is generally accepted that large data warehouse fact tables do not need a PK, mostly because

i) you can rely on rowid for unique identification, and
ii) you will not need to reference the table through a foreign key.

I expect that is not the case here however, so I'm just being pedantic.

Quote:

---

Originally posted by slimdave
I expect that is not the case here however, so I'm just being pedantic.

---

Well.... quit it :D