Assume i have a table
Table: (ID INTEGER PRIMARY KEY NOT NULL, NAME STRING)
Assume ID are in increasing order.
Now the query "update Table set ID = ID-1"
will work fine; At the time of setting the ID = ID -1, the value ID - 1 is not in the table hence there wont be any conflicts (PRIMARY KEY).
Whereas update Table set ID = ID + 1 will cause problems. Is there a way to state the sql to start the operation from its last row rather than from the first row? It can be done using a subquery (select rowid from Table order by ID desc). Is it possible without using subquery?


Regards,
Phani

__________________
Regards,
Phani

By any chance setting it to Autoincrement helps?

_________________________
- Vijay G
Strive for Perfection

Vijay u didnt get my question.
Assume i already have a table with data
1 Akhil
2 Arun
3 Deepak
4 Manu
5 Rejith

Now Someone named Abhishek needs to be added in the que. Since Abhishek < Akhil. I must make the ID of Abhishek 1, (assume strings are in sorted order) hence i need to increment the ID of all other names by 1. But if i try to update the value of Akhil, his new ID will become 2, which will give rise to a conflict since (2, Arun) is already there in the table. But increment starts from the last row then i will have any conflicts.


Regards,
Phani

Yes, now I understand that, may be your first post was not explaining that. I am not sure if you can do that without subquery or multiple queries involved.


All I can think of is basically you need to find the rows between which your new value will fit in.

a) Move the rows that are greater than the new row to be inserted, in to a temp table, then insert the new row and move the rows from temp table back to the original table with incremented Id values.

OR

b) Increment the ID value of rows that are greater than the new row, and then insert this new row.

Hope that helps.
Cheers.

_________________________
- Vijay G
Strive for Perfection

No need to do move and other stuff. Its gonna take time. It will be better than to have update id = id + 1 where Id in (select Id from Table order by ID desc). But then it uses subquery. Can we have "order by" associated with Update statement?


Regards,
Phani

DMLs doesn't support ORDER BY clause. AFAIK, no SQL language allows that.

_________________________
- Vijay G
Strive for Perfection

Most SQL language's support Order By with select clause

http://www.w3schools.com/sql/sql_orderby.asp

Sqlite doesn't allow order by to be associated with Update, Insert, delete.

Regards,
Phani

No SQL language supports an Order By clause with Update, Insert, or Delete; those commands don't return a result set hence there is nothing to order by.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========