Possible to use INNER JOIN with DELETE

Hello all,

Well, the subject says it all. I'd really like to be able to delete records conditionally based on matched values to another table. Viz

DELETE
FROM table_a INNER JOIN table_b" ON table_a.JobNumber = table_b.JobNumber WHERE (table_b.closed Is Not Null) AND (table_a.exported Is True) AND (table_a.JobNumber LIKE '00_____' );

Is this possible with Postgresql v 7.3?

If not, does anyone know of a clever way to get around this limitation?

Thx
o

Seems to work for me on an ancient version of PG (7.1.3).

Just tried this query out on one of my DBs as a test

DELETE FROM announcement WHERE announcement.ann_dept = department.dept_no

Very similar to your query, just tack on the additional criteria with ANDs and you should be good.

Let us know if you have any probs.

Hmmm. Not quite.

The table that I am deleting from (call it table_a) has a field "JobNumber" that relates to table_b with a field named "JobNumber". Based on the related information in table_b, namely whether that job is closed or not, I need to delete from table_a.
Your example doesn't quite do that. Your example would delete all records where table_a.JobNumber = table_b.JobNumber.

By definition, *all* records in table_a will match this condition (since all job numbers used in table_a are kept in table_b). I need the query to do an INNER JOIN based on a match and go a step further: test the *related* value in table_b when jobnumber=jobnumber.

The example I give gives syntax errors and I've read somewhere that mySQL didn't do this sort of thing in past versions; I'm thinking postgresql might have a similar limitation.

Of course, I might be completely misunderstanding you......

Yeah, that's why in my post I said



This doesn't work for you?

DELETE
FROM table_a WHERE table_a.JobNumber = table_b.JobNumber AND (table_b.closed IS NOT NULL) AND (table_a.exported IS TRUE) AND (table_a.JobNumber LIKE '00_____' );

What errors do you get if you try that?
-b

Ugh. No Errors. That worked beautifully.

This is a classic example of doing something one way for so long that one fails to see how an alternative might work until one is hit over the head with it.

I was so fixated on INNER JOINs I didn't even consider how a WHERE statement would do the same thing, essentially. As I suspected, I completely missed the point of your original post.


Anyway, thank you for the cuff-to-the-head I was in desperate need of.

o