Reordering from a query in Oracle

When I run it in my Server Explorer window it errors:

ORA-00923: FROM keyword not found where expected.

Is this something I can run from a ExecuteNonQuery? I don't have to create a stored procedure?

thanks.

Actually you can join for an update. It is an inline view and will be faster than a subquery, but that may not be important for this question since angelIII has already provided an option.

A simple update against an inline view is:

UPDATE (SELECT a.col1 as a_col1, b.col1 as b_col1
        FROM a INNER JOIN b
        ON (a.id = b.id)
       )
SET a_col1 = b_col1
;
                                              

1:
2:
3:
4:
5:
6:

I missed a "," in my query

with sq as (
  select questionValueID, row_number() over (order by ListOrder, 1) newListOrder
    from TBL_QUESTIONVALUE
   where questionSetId = 1 
      or (questionSetId is Null and 1 is Null)
)
Update TBL_QUESTIONVALUE qv
  Set ListOrder = ( select sq.newListOrder
                      from sq
                     where sq.questionValueId = qv.questionValueID 
                   )

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

Gives the same error.

with sq as (
  select questionValueID, row_number() over (order by ListOrder, 1) newListOrder
    from TBL_QUESTIONVALUE
   where questionSetId = 1 
      or (questionSetId is Null and 1 is Null)
)
Update TBL_QUESTIONVALUE qv
  Set ListOrder = ( select sq.newListOrder
                      from sq
                     where sq.questionValueId = qv.questionValueID 
                   )

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

>Is this something I can run from a ExecuteNonQuery? I don't have to create a stored procedure?
ah, I see that now..
possibly yes. I have seen with both OleDbconnection/command as well as over linked server, this is problematic.
a stored procedure (or function with autonomous transaction) will help there.

None-the-less, it still errors, and I asked for a sql query command. This is not working for me.

What can I do to get it to work?

thanks.

In regard to the number 1... I said:

>>Ultimately, I'll be replacing the "1" value with a parameter value, but for now I'm hard-coding it to the ID #1.

So yes, the 1 parameter value could ultimately be null.

>>Lastly, you are ordering by listOrder, so it seems you want to order a subset of questions,

Correct.

where qv.questionSetId = 1
Error at line 1
ORA-00933: SQL command not properly ended

this much is good:

select questionValueID, row_number() over (order by ListOrder, 1) newListOrder
                    from TBL_QUESTIONVALUE
                    where questionSetId = 1

It returns the newlistorder as expected. But when combining the whole thing, it errors as per previous post.

what version of oracle are you using?

I tested it on this test table prior to posting the code snippet.

SQL> desc tbl_questionvalue
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 QUESTIONVALUEID                           NOT NULL NUMBER(38)
 QUESTIONSETID                                      NUMBER(38)
 LISTORDER                                          NUMBER(38)


Post your table structure so we can all be on the same page.

I am using Oracle10g.

Attached is the sql I used to create the tables.

create sequence QuestionSetId_seq start with 1;
DROP TABLE tbl_QuestionSet;
CREATE TABLE tbl_QuestionSet
(
  QuestionSetId				   NUMBER       NOT NULL,
  QuestionSetName			   VARCHAR2(50),
  QuestionTypeListValueId     NUMBER,  
  dateCreated    DATE,
  dateUpdated    DATE,
  userCreatedId  NUMBER,
  userUpdatedId  NUMBER
)
 
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;
 
 
ALTER TABLE tbl_QuestionSet ADD (
  CONSTRAINT tbl_QuestionSet_PK
 PRIMARY KEY
 (QuestionSetId));
 
 
 
create sequence QuestionValueId_seq start with 1;
 
DROP TABLE tbl_QuestionValue;
CREATE TABLE tbl_QuestionValue
(
  QuestionValueId			   NUMBER     NOT NULL,
  QuestionSetId				   NUMBER,
  QuestionValueName     VARCHAR2(2000),
  QuestionToolTip  VARCHAR2(2000),
  ListOrder					NUMBER,
  YesNoIncludedListValueId				NUMBER,
  TextRequiredListValueId	NUMBER,
  StatusListValueId			NUMBER,
  DateCreated    DATE,
  DateUpdated    DATE,
  UserCreatedId  NUMBER,
  UserUpdatedId  NUMBER
)
 
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;
 
 
ALTER TABLE tbl_QuestionValue ADD (
  CONSTRAINT tbl_QuestionValue_PK
 PRIMARY KEY
 (QuestionValueId));
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:

wait a second.

I just now ran it and it updated 6 rows.

yesterday it gave methat error.

go figure.

This is nuts. I ran it once, and it ran and updated 6 rows.

I've tried it numerous times since, and it runs more than 6 minutes and I finally cancel it.

Why would that happen?

I can't get it to run again. Any ideas?

Sorry.

I closed Toad and restarted it, and it ran fine again.

Then I reran it immediately after and it hung up again.

Must be a toad issue, maybe. If you have ideas, I'd appreciate it, but I'm going to close this out.

Thanks!

thanks!

it looks like you have locking / transaction issues...
you might need to "commit" before rerunning the next udpate ...