Thread: Delete from right join

Delete from right join

From

"Chad Thompson"

Date:

07 January 2003, 18:10:36

I have the following query

select project_lists.*
FROM temp_lol RIGHT JOIN project_lists ON temp_lol.project_id = project_lists.project_id
WHERE temp_lol.project_id Is Null

I run this on my postgresql 7.2.x database and it returns some 3k records.

If i use a join or a left join it returns no data.

If I do this however

DELETE

FROM temp_lol RIGHT JOIN project_lists ON temp_lol.project_id = project_lists.project_id
WHERE temp_lol.project_id Is Null

I get an error, Parse error at or near right.

I realize that right joins are evil, but I dont know what else to use to delete these 3k records from my table.

Any Ideas?

Thanks

Chad

Re: Delete from right join

From

"Josh Berkus"

Date:

07 January 2003, 18:57:03

Chad,

> If I do this however
> DELETE
> FROM temp_lol RIGHT JOIN project_lists ON temp_lol.project_id =
> project_lists.project_id
> WHERE temp_lol.project_id Is Null

You can't delete on a JOIN.  That would imply that you were deleting
from both tables in a single statement, which is not permitted.

Instead, assuming that you want to delete from temp_lol:

DELETE
FROM temp_lol
WHERE NOT EXISTS (
     select project_id FROM project_lists
     WHERE temp_lol.project_id = project_lists.project_id );

I'd also suggest picking up a SQL book, such as "SQL Queries for Mere
Mortals" to help you with this kind of stuff.

-Josh Berkus