deleting a row using inner join? possible?

[bhob]

hey guys,

i have a query wherein one table has the same id as the other using inner join..

SELECT c.cat_name, g.maxscore, g.description
FROM grp_performance
INNER JOIN category
ON g.cat_id = c.cat_id

Is it possible to use inner join when deleting a row? like..

DELETE FROM grp_performance
INNER JOIN category
ON g.cat_id = c.cat_id

[debasisdas]

Yes.

[adam_k]

Like debasisdas said, yes you can. But this won't delete the record from both tables, if that's what you had in mind. It's use is to delete from grp_performance, using c.cat_name as an example.

If you need to delete from both tables with 1 delete, you will need a trigger.

[bhob]

odd.. the innerjoin didn't work.. :( so i used this sql:

DELETE from grp_performance
where grp_performance.cat_id = (Select cat_id from category where cat_id=catID_ AND cat_name = catName_)
AND description = description_ AND maxsxore = maxsxore_;

i only want to delete the cat_id in the grp_performance table as well as the description, maxscore, period, and date without deleting the records in the category table.

Even though you CAN perform DML on views (sometimes, NOT always)... You shouldn't.
Stick with performing DML on base tables.