Could someone enlighten me how to delete rows from a table when selecting the rows to delete from the same table: my job consists of an ODBC source stage which selects the rows to be deleted from a table, the primary key of the rows is sent through a tansformer stage to a target ODBC stage where I want to delete them.
The problem I encounter is that the delete doesn't work since the rows to be deleted are still locked by the "source" stage. Does a solution exists for this?
Replacing the ODBC stages by a specific database plugin (DB2) where I can set the isolation level also does not work.
Regards,
Ogmios
Deletion of rows
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This may seem a weird answer.
Use user-defined SQL in your source stage (that's right, your source stage) that deletes the rows.
You can use job parameters in the SQL. For example:
DELETE FROM tablename WHERE col1 < '#Criterion#';
(Don't forget that you still have to surround string literals in SQL with single quotes; depending on the DBMS you may also need to surround identifiers (table and column names) with double quotes.)
The job itself will then process 0 rows, which should be quick.
Use user-defined SQL in your source stage (that's right, your source stage) that deletes the rows.
You can use job parameters in the SQL. For example:
DELETE FROM tablename WHERE col1 < '#Criterion#';
(Don't forget that you still have to surround string literals in SQL with single quotes; depending on the DBMS you may also need to surround identifiers (table and column names) with double quotes.)
The job itself will then process 0 rows, which should be quick.