Click here to Skip to main content
15,920,896 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a problem to delete data from two table in the same sql and i have join between to table . how can delete

for example

C#
CommandText = "delete sub_main from sub_main inner join is_main on sub_main.action=is_main.action where row.sub_main='" + dataGridView1.CurrentRow.Cells[1].Value + "' and action.sub_main='" + dataGridView1.CurrentRow.Cells[0].Value + "' and action.is_main='" + dataGridView1.CurrentRow.Cells[2].Value + "'";
Posted
Updated 30-Oct-15 3:48am
v4
Comments
[no name] 28-Oct-15 3:31am    
You want to delete data or select data from two table. Because in your question it is selecting but in question title it need to delete. Please confirm.
Sinisa Hajnal 28-Oct-15 3:39am    
Simple answer is you don't. A bit more: you can write stored procedure that will accept some id that defines what needs to be deleted (in your case id). You loop through ids (5 ,6) and call the procedure that deletes both. Or you send XML parameter with all values, parse in the database and delete from there (this way you have to construct the XML, but you don't keep open transaction in the code.
Naveen.Sanagasetti 28-Oct-15 6:04am    
Could you please elaborate your problem
Richard Deeming 30-Oct-15 9:59am    
Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

1 solution

It is not possible to delete rows from multiple tables using single DELETE statement in SQL SERVER.

Create a foreign key between these two tables and use ON DELETE CASCADE.

Have a look at this one

Single SQL Query to delete records in two tables[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900