CODEWITHSUNDEEP
c#sqlsystem.data.sqlite
Tirafesi
Tirafesi

Reputation: 1479

SQLite Update Limit

How can I use the LIMIT clause in an UPDATE in SQLite?

I tried adding the option to the SQLConnection but it didn't work, I get the error SQL logic error near LIMIT

SQLiteConnection connection = new SQLiteConnection("Data Source=" + outpath + ";Version=3;foreign keys=true;sqlite enable update delete limit=true");

string sql = @"
    UPDATE MyTable
    SET userId = @userId2
    WHERE userId = @userId1
    LIMIT @amount
";

In case it's not possible to make the LIMIT clause work, how else would I limit the amount of records that are updated?

Upvotes: 2

Views: 1142

Answers (1)

Header Footer
Header Footer

Reputation: 161

You can use subquery to fetch records viable for userId field update.

Let's say this is your MyTable contents:

id  userId
1   1
2   1
3   1
4   2
5   2

If you want to change only 2 records of userId=1 into userId=3. You can select those 2 records by ID:

UPDATE MyTable
SET userId=3
WHERE id IN 
    (select id 
     from users 
     WHERE userId=1 
     LIMIT 2)

The subquery gets id values of first 2 records matching the criteria and passes them to WHERE id IN.

The first 2 records will be affected, because no ORDER BY have been used within subquery.

Upvotes: 2

Related Questions