-
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 )
- 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}]...];
Last edited by stecal; 10-30-2002 at 05:13 PM.
-
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)
Jeff Hunter
-
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
-
And this is my 1000th post!!!!!!!!!!
(okay, so Jeff is un-impressed, but it made me happy )
- Chris
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|