How can I do batch update?

For example, I have 5 queries:

UPDATE Catalogue SET Field1 = 3 WHERE ID = 1336
UPDATE Catalogue SET Field1 = 5 WHERE ID = 1337
UPDATE Catalogue SET Field1 = 6 WHERE ID = 1338
UPDATE Catalogue SET Field1 = 6 WHERE ID = 1339
UPDATE Catalogue SET Field1 = 6 WHERE ID = 1340

I want to execute all queries in the one transaction and one ExecSql statement.

Which component I have to use and which queries devider (";" - ?) I can use?

Creatorre

The simple way to do it would be to use a TEDBScript and EXECUTE IMMEDIATE. It is possible to wrap all the statements in a transaction but I'm not sure how that works with EXECUTE IMMEDIATE.


SCRIPT
BEGIN
START TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'UPDATE Catalogue SET Field1 = 3 WHERE ID = 1336';
EXECUTE IMMEDIATE 'UPDATE Catalogue SET Field1 = 5 WHERE ID = 1337';
EXECUTE IMMEDIATE 'UPDATE Catalogue SET Field1 = 6 WHERE ID = 1338';
EXECUTE IMMEDIATE 'UPDATE Catalogue SET Field1 = 6 WHERE ID = 1339';
EXECUTE IMMEDIATE 'UPDATE Catalogue SET Field1 = 6 WHERE ID = 1340';
COMMIT;
EXCEPTION
ROLLBACK;
END;
END

The component is executed using ExecScript.

Roy Lambert

Roy Lambert wrote:

Creatorre

The simple way to do it would be to use a TEDBScript and EXECUTE IMMEDIATE. It is possible to wrap all the statements in a transaction but I'm not sure how that works with EXECUTE IMMEDIATE.


SCRIPT
BEGIN
START TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'UPDATE Catalogue SET Field1 = 3 WHERE ID = 1336';
EXECUTE IMMEDIATE 'UPDATE Catalogue SET Field1 = 5 WHERE ID = 1337';
EXECUTE IMMEDIATE 'UPDATE Catalogue SET Field1 = 6 WHERE ID = 1338';
EXECUTE IMMEDIATE 'UPDATE Catalogue SET Field1 = 6 WHERE ID = 1339';
EXECUTE IMMEDIATE 'UPDATE Catalogue SET Field1 = 6 WHERE ID = 1340';
COMMIT;
EXCEPTION
ROLLBACK;
END;
END

The component is executed using ExecScript.

Roy Lambert

Thanks, Roy. I'll try it.