#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    5
    Rep Power
    0

    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
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    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.
    PostgreSQL, it's what's for dinner...
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    5
    Rep Power
    0
    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......
  6. #4
  7. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Your example would delete all records where table_a.JobNumber = table_b.JobNumber.
    Yeah, that's why in my post I said

    Very similar to your query, just tack on the additional criteria with ANDs and you should be good.
    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
    PostgreSQL, it's what's for dinner...
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    5
    Rep Power
    0
    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

IMN logo majestic logo threadwatch logo seochat tools logo