how to delete a row from two table by passing single variable

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

i used below code to delete rows from two table by passing single value but i am getting error like

Incorrect syntax near ','

SQL

DELETE messages ,usersmessages FROM messages INNER JOIN usersmessages
WHERE messages.messageid= usersmessages.messageid and messages.messageid = 1

Posted 17-Dec-12 22:27pm

Umapathi K

Updated 17-Dec-12 23:37pm

sisir patro

v2

Add a Solution

Comments

Rai Pawan 18-Dec-12 4:41am

you can use foreign key references with cascading delete.

Umapathi K 18-Dec-12 4:46am

i didnt use any primary key and foreign key

skydger 18-Dec-12 5:51am

If you have no any foreign keys with cascade delete constraint then you should create a procedure which combines two separate deletes
DELETE FROM usermessages
WHERE messageid = 1

DELETE FROM messages
WHERE messageid = 1

You can also create a trigger on one of these tables on delete action.

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

__TR__ · Answer 1 · 2012-12-17T23:42:00

Solution 2

Try

SQL

BEGIN TRY  
  BEGIN TRANSACTION  

   DELETE FROM usersmessages
   WHERE  messageid = 1

   DELETE FROM messages 
   WHERE  messageid = 1


  COMMIT TRANSACTION  
 END TRY  
 BEGIN CATCH  
  ROLLBACK TRANSACTION  
 END CATCH

Posted 17-Dec-12 23:42pm

__TR__

Updated 17-Dec-12 23:43pm

v2

Nikhil Khandelwal (MCTS, Web 4.0) · Answer 2 · 2012-12-17T23:41:00

SQL

begin transaction;

   declare @deletedIds table ( id int );

   delete t1
   output deleted.id into @deletedIds;
   from table1 t1
    join table2 t2
      on t2.id = t1.id
    join table3 t3
      on t3.id = t2.id;

   delete t2
   from table2 t2
    join @deletedIds d
      on d.id = t2.id;

   delete t3
   from table3 t3 ...

commit transaction;

Happy Coding :)

pranathis012 · Answer 3 · 2012-12-17T23:43:00

Solution 3

Please Read the DELETE Query Syntax

SQL

DELETE FROM table_name
WHERE some_column=some_value

Posted 17-Dec-12 23:43pm

pranathis012

sisir patro · Answer 4 · 2012-12-17T23:43:00

Solution 4

Hi,

Your syntax is wrong here. Use like the following.

SQL

DELETE  FROM messages INNER JOIN usersmessages
WHERE messages.messageid= usersmessages.messageid and messages.messageid = 1

Thanks