Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLITE bulk UPDATE statement

Tags:

sql

sqlite

I want to perform many SQL UPDATE Statements like these:

UPDATE cityd SET time_zone='-7.000000' WHERE locId = 173567;
UPDATE cityd SET time_zone='-8.000000' WHERE locId = 173568;
UPDATE cityd SET time_zone='-6.000000' WHERE locId = 173569;
UPDATE cityd SET time_zone='-5.000000' WHERE locId = 173570;
UPDATE cityd SET time_zone='-6.000000' WHERE locId = 173571;

I want to optimize the transaction time, so I need to use BEGIN TRANSACTION/COMMIT pair. How to write this in SQL Syntax in SQLtite Manager?

Edit: When I use the standard syntax in SQLite Manager I receive this error message: "SQLiteManager: BEGIN TRANSACTION; [ cannot start a transaction within a transaction "

like image 957
Ahmed Said Avatar asked Mar 19 '13 14:03

Ahmed Said


People also ask

Can we do bulk UPDATE in SQL?

UPDATE in Bulk It's a faster update than a row by row operation, but this is best used when updating limited rows. A bulk update is an expensive operation in terms of query cost, because it takes more resources for the single update operation. It also takes time for the update to be logged in the transaction log.

What is bulk UPDATE?

A bulk update definition specifies a number of conditions and a single update function. A policy must satisfy all the specified conditions in order for it to updated by the function. Bulk updates are executed through a global activity. The bulk update definition code is a parameter of this activity.

Does SQLite support select for UPDATE?

For example, MySQL supports SELECT FOR UPDATE, but SQLite does not.


2 Answers

According on SQL Documention, there are two supported syntax of CASE

CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END

CASE WHEN x = w1 THEN r1 WHEN x = w2 THEN r2 ELSE r3 END

So your multiple UPDATE statements can be further simplified into

UPDATE  cityd
SET     time_zone = CASE locId
                        WHEN 173567 THEN '-7.000000'
                        WHEN 173568 THEN '-8.000000'
                        WHEN 173569 THEN '-6.000000'
                        WHEN 173570 THEN '-5.000000'
                        WHEN 173571 THEN '-6.000000'
                    END
WHERE   locId IN (173567, 173568, 173569, 173570, 173571)
like image 169
John Woo Avatar answered Sep 20 '22 09:09

John Woo


BEGIN TRANSACTION;
.....YOUR SQL Statements here

COMMIT;
like image 31
Yaqub Ahmad Avatar answered Sep 21 '22 09:09

Yaqub Ahmad