Delete

[aaraaayen 0]

hi friends, I want a delete query to delete rows from multiple tables. but all the tables containg one command field as 'id'. and the deletion based on some input given to this common field at run time in msaccess. Please help. I am trying like this. But it gives Could not delete message. DELETE contact.*, address.* FROM contact, address WHERE contact.consultantid=[@consultantid] and address.consultantid=[@consultantid]; Thanks in advance. Regards,

Prya

[Pete OHanlon]

You cannot perform a delete on two tables at once. You can perform two deletes, and this is what you should do.

Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.

[Russell Jones]

also you could set up cascading deletes and then if you delete from one table the record gets removed from the related table. I'm never sure how much i like cascading deletes though. Pete's method is the one i would use in my code you can pass the two commands separated by ; if you only want to make 1 call (i believe) Russ

[albCode]

declare @ID as int set @ID=41 --instad 41 use parameter DELETE FROM contact WHERE ID =@ID DELETE FROM addressWHERE ID =@ID

---

"My advice to you is to get married. If you find a good wife, you will be happy; if not, you will become a philosopher." Socrates

[i j russell]

You also need to be careful about the order of the delete statements if you are using foreign key constraints. In this case, you would need to delete from the address table before the contact table. Ian

[Ritwik Sanyal]

If you are using SQL Server you can write a FOR DELETE trigger on the table 'contact'. Try this: - -------------------------------------------------------------------------------------------------- CREATE PROCEDURE DeleteContact @consultantid int AS DELETE FROM contact WHERE consultantid=@consultantid -------------------------------------------------------------------------------------------------- Now create a trigger on the table 'contact': - -------------------------------------------------------------------------------------------------- CREATE TRIGGER DeleteAddress ON contact FOR DELETE AS CREATE TABLE #DeletedContacts ( consultantid int ) INSERT INTO #DeletedContacts SELECT consultantid FROM deleted DELETE FROM address WHERE consultantid IN (SELECT consultantid FROM deleted) -------------------------------------------------------------------------------------------------- Now, you execute the stored procedure: EXEC DeleteContact After it deletes from the table 'contact' the trigger will be excuted and it will delete corresponsing rows from the table 'address'.

I am a Software Developer using C# on ASP.NET.