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.

[hfx642]

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