Database updating sort order
I need to re-sort the way the strings are displayed in the client so that they are alphabetical ( The order can be changed in the client and that is the official method but it's such a clunky method that it would literally take days and thousands of mouse-clicks to do this. To do this in SQL I need to sort the whole table on column E and then sort column F ascending and then UPDATE the whole table.
So far as I can see MS SQL does not allow an UPDATE including an ORDER BY and I'm struggling to find a coherent way to do this.
If they aren't really duplicate rows, then use the other columns that aren't identical to correctly sort out the ONE row you really want.
(then get a proper primary key created on that table ASAP) But if you really must do something like this, what's wrong with a little PL/SQL block?
It appears that what you want to do is to update the row having the lowest value for col2 where col1=0.
You could use this: UPDATE MY_TABLE SET col1 = :new Value WHERE col1 = 0 AND col2 = (SELECT MIN(col2) FROM MY_TABLE WHERE col1 = 0); Of course if the table is very large and there is no index on col2, this could be slow. DECLARE CURSOR c IS SELECT col1 FROM my_table WHERE col1 = 0 ORDER BY col2 FOR UPDATE OF col1; r c%ROWTYPE; BEGIN OPEN c; FETCH c INTO r; IF c%FOUND THEN UPDATE my_table SET col1 = :new Value WHERE CURRENT OF c; END IF; CLOSE c; END; An advantage here is that if the min(col2) value is in multiple rows with col1 = 0, the first example will update all of them while the second example will only update one. Hope this helps Ken Denny I am trying to do the following: EXEC SQL UPDATE MY TABLE SET COL1 = :new Value WHERE COL1 = 0 AND ROWNUM = 1 ORDER BY COL2; (index on COL1, COL2) Pro*C does not process the "ORDER BY" statement.
We save this order in a database column that is an integer and is in sequential order.
Furthermore, if you don't specify an 'order by clause' the rows can be in any order.
Could I do something like this: UPDATE MY_TABLE SET col1 = :new Value WHERE col2 = (SELECT MIN(col2) FROM MY_TABLE WHERE ROWNUM = 1 AND col1 = 0); If this is OK, I was wondering what are the performance implications of moving the "WHERE" clause to the outer update statement (index is on col2 as well), i.e.: UPDATE MY_TABLE SET col1 = :new Value WHERE col1 = 0 AND col2 = (SELECT MIN(col2) FROM MY_TABLE WHERE ROWNUM = 1); Thanks Sandra You need the "where col1 = 0" both places.
Laravel is a web application framework with expressive, elegant syntax.
We believe development must be an enjoyable, creative experience to be truly fulfilling.