CODEWITHSUNDEEP
sqlsqlite
Omar Abid
Omar Abid

Reputation: 15976

Updating rows in order with SQL

I have a table with 4 columns. The first column is unique for each row, but it's a string (URL format). I want to update my table, but instead of using "WHERE", I want to update the rows in order. The first query will update the first row, the second query updates the second row and so on.

What's the SQL code for that? I'm using Sqlite.

Edit: My table schema

CREATE table (
url varchar(150),
views int(5),
clicks int(5)
)

Edit2: What I'm doing right now is a loop of SQL queries

update table set views = 5, click = 10 where url = "http://someurl.com";

There is around 4 million records in the database. It's taking around 16 seconds in my server to make the update. Since the loop update the row in order, so the first query update the first row; I'm thinking if updating the rows in order could be faster than using the WHERE clause which needs to browse 4 million rows.

Upvotes: 0

Views: 354

Answers (1)

Callie J
Callie J

Reputation: 31296

You can't do what you want without using WHERE as this is the only way to select rows from a table for reading, updating or deleting. So you will want to use:

UPDATE table SET url = ... WHERE url = '<whatever>'

HOWEVER... SqlLite has an extra feature - the autogenerated column, ROWID. You can use this column in queries. You don't see this data by default, so if you want the data within it you need to explicitly request it, e.g:

SELECT ROWID, * FROM table

What this means is that you may be able to do what you want referencing this column directly:

UPDATE table SET url = ... WHERE ROWID = 1

you still need to use the WHERE clause, but this allows you to access the rows in insert order without doing anything else.

CAVEAT
ROWID effectively stores the INSERT order of the rows. If you delete rows from the table, the ROWIDs for remaining rows will NOT change - hence it is possible to have gaps in the ROWID sequence. This is by design and there is no workaround short of re-creating the table and re-populating the data.

PORTABILITY
Note that this only applies to SQLite - you may not be able to do the same thing with other SQL engines should you ever need to port this. It would be MUCH better to add an EXPLICIT auto-number column (aka an IDENTITY field) that you can use and manage.

Upvotes: 1

Related Questions