Обсуждение: Delete from right join

Поиск
Список
Период
Сортировка

Delete from right join

От
"Chad Thompson"
Дата:
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

От
"Josh Berkus"
Дата:
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