Click here to Skip to main content
12,956,512 members (78,437 online)
Rate this:
 
Please Sign up or sign in to vote.
i used below code to delete rows from two table by passing single value but i am getting error like

Incorrect syntax near ','

DELETE messages ,usersmessages FROM messages INNER JOIN usersmessages
WHERE messages.messageid= usersmessages.messageid and messages.messageid = 1
Posted 17-Dec-12 22:27pm
Updated 17-Dec-12 23:37pm
v2
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Try

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  
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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 :)
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Please Read the DELETE Query Syntax

DELETE FROM table_name
WHERE some_column=some_value
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Hi,

Your syntax is wrong here. Use like the following.

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



Thanks
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

Hi

Can you try the below query:

DELETE MESSAGES  FROM MESSAGES INNER JOIN USERSMESSAGES
ON MESSAGES.MESSAGEID= USERSMESSAGES.MESSAGEID WHERE MESSAGES.MESSAGEID = 1
  Permalink  

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

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,869
CHill60 3,550
Maciej Los 3,302
ppolymorphe 2,080
Jochen Arndt 1,973


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 18 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100