Getting an error with the following:

DELETE
FROM Table1 INNER JOIN Table2 ON Table1.FieldName = Table2.FieldName
Where (((Table2.FieldName) LIKE "criteria"));


Table 2 contains a primary key that ties the two tables together. It also contains another field which allows me to select specific record types.
It's a one-to-many with Table1.

Table 1 contains numeric data that is associated with Table2. No primary key.


Error Msg is: Specify the table containing the records you want to delete.

Can someone set me straight?

DELETE FROM Table1 where Table1.FieldName in (select Table2.FieldName from table2 Where Table2.FieldName LIKE "criteria");

Andy

Seems to work.

Thank you

Joins (or views defined on joins) are never updatable, i.e., cannot be the subject of an insert, update or delete.

Thanks....

It seems the previous solution is not the best for my situation. I ran it and it seemed to work but after 24hrs only about 200 records were deleted with no end in sight. I had to force the command to end. Not very efficient.

You might try a correlated subquery:

DELETE
FROM Table1
WHERE EXISTS (
SELECT * FROM Table2
WHERE Table1.FieldName = Table2.FieldName
AND Table2.FieldName LIKE "criteria") ;

Slow deletes are very often due to FK constraints.

If you can, drop all FK constraints (pointing both to and from the table in question) and recreate the FK's after the delete.

Dropping FK's from this table to others is not necessary. DB2 does not have to check those constraints in case of a DELETE operation.

stolze, that would be logic, but I have found out it does influences delete speed dramatically.

So now I drop all FK's, both defined on the table itself as on other tables that reference the "delete" table. Perhaps the latest DB2 versions behave different, I use this method for years now.

I followed the suggestion by ARWinner..... My target table has 5000 records in it.... I'm trying to delete 32 of them.... ran overnight without returning my cursor.

Will try removing FKs from this table to see what results that provides.

When putting back the FKs, your tablespace(s) will be put in check pending state, so don't forget to run CHECK DATA after that (at least, on z/OS; no idea what's the equivalent on other platforms)

DB2 LUW checks the FKs right away - no need for CHECK DATA or - what a closer equivalent would be - the SET INTEGRITY statement.

Wat are your results ?