Deleting using InnerJoin

[dayakar_dn]

Hi, I am having two table which are related with primary and foreign key constraints. I want to delete records from both the tables using innerjoin. I applied like this, delete sports,studentregtable from studentregtable as sr INNER JOIN sports sp ON sr.registerNo = sp.RegisterNumber where RegisterNumber = '123' but it is giving an error like incorrect syntax near ",". can you please help me if you know the solution.

[Chris Meech]

You have to delete from each table separately. Try the following

delete from studentregtable where registerNo = '123';
delete from sports where RegisterNumber = '123';

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

[SilimSayo]

If one of the tables has a foreign refencing the other table, start with the table with the foreign key.

[David Skelly]

If this is SQL Server, you can specify to cascade deletes when you create the foreign key constraint. Then, you can delete from the parent table and the database will automatically delete the corresponding rows from the child table. Personally, I don't like this because it makes me feel as if I'm not in control and I get nervous when the DBMS does things behind my back like that. But some people swear by it, so YMMV. Sybase and DB2 both have this same cascade ability on a foreign key; not sure about Oracle but I guess it probably does.

[nagendrathecoder]

Yes, it does. :)