Deleting from 2 tables?

Hi, I am pretty new to SQL and know that I am probably going around this the wrong way.

I want to make a stored proc that deletes rows from table 1 and delete rows from table 2 where the common link is the id.

Any help would be greatly appreciated!

Many thanks

moopIf you have a relationship set between the tables, you can set the action in table 1 to "On Delete Cascade".
|||*blush*

Thanks for the reply. I just realised that unlike mySql you can issue two deletes in one stored proc like so

DELETE FROM tblSupplierType WHERE supplierType_id = @.supplierType_id;
DELETE FROM tblSuppliers WHERE supplierType_id = @.supplierType_id

MAny thanks for the reply|||I would probably still use the On Delete Cascade option, because ifsomeone comes along and changes your SQL statement later, you'll have alot of stranded Supplier records. With On Delete Cascade, it allhappens as part of the original delete.
Either way I would suggest wrapping those two statements in atransaction, and you'll need to reverse the order in which you've shownthem.
|||

I'm trying this using the Club Starter Kit, creating a relationship between the Albums and Images table, where the PK Album ID in the Albums table has a relationship to the Images table via the album field.

When I create the relationship and use the OnDeleteCascase option, I'm receiving this error:

'Albums' table saved successfully
'images' table
- Unable to create relationship 'FK_images_Albums'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_images_Albums". The conflict occurred in database "C:\DOCUMENTS AND SETTINGS\GARY\MY DOCUMENTS\VISUAL STUDIO 2005\WEBSITES\MyWebSite\MyWebSite.MDF", table "dbo.Albums", column 'albumid'.

any thoughts on what might be wrong?

I know the PK ablumid is an identity field. Does that matter?

Thanks,

Gary

|||

Disregard my previous post. I was able to get the Cascade to work with Delete.

Thanks,

Gary

Deleting all records in a table

I am trying to write a transact sql statment to delete all records in a table. What is wrong with the following statment?
DELETE * FROM tblPerson
GO
ThanksShould be just

DELETE FROM tblperson

unless you are using a WHERE CLAUSE such as

DELETE FROM tblperson WHERE id = 1|||Depending on the size of the table, you may also wish to consider truncating it. In general, a TRUNCATE statement will perform much faster than a DELETE. Try:

TRUNCATE TABLE tblPerson

Originally posted by Sia
I am trying to write a transact sql statment to delete all records in a table. What is wrong with the following statment?

DELETE * FROM tblPerson
GO

Thanks|||Originally posted by hmscott
Depending on the size of the table, you may also wish to consider truncating it. In general, a TRUNCATE statement will perform much faster than a DELETE. Try:

TRUNCATE TABLE tblPerson