how to delete productid from multiple table

Question

i want to delete a product id when click a button, i only able to delete 1 table how to do with my query

enter image description here

protected void btnDelete_Click(object sender, EventArgs e)
        {
            string connection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            SqlConnection connection1 = new SqlConnection(connection);
            string sqlStatement = "DELETE FROM Product WHERE ProductID = @pid";

            try
            {
                connection1.Open();
                SqlCommand cmd = new SqlCommand(sqlStatement, connection1);
                cmd.Parameters.AddWithValue("@pid", Id);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

            }
            finally
            {
                connection1.Close();
            }
        }

set cascading deletes in the main table – SQLMason Dec 26 '13 at 17:17 — SQLMason, Dec 26 '13 at 17:17
Have multiple delete statements for each table – SQLMason Dec 26 '13 at 17:18 — SQLMason, Dec 26 '13 at 17:18
cascading deletes in the main table how to set? – Mickey Dec 26 '13 at 17:28 — Mickey, Dec 26 '13 at 17:28
using google or reading the link in the answer – Vland Dec 26 '13 at 17:30 — Vland, Dec 26 '13 at 17:30

score 0 · Accepted Answer · edited May 23 '17 at 11:56

0

Check ON DELETE CASCADE in MySql

or write more queries to first delete the records linked to your Product table.

eg.

DELETE from ProductSideImage WHERE ProductID = 1;
DELETE from ProductImage WHERE ProductID = 1;
DELETE from ProductStock WHERE ProductID = 1;
DELETE from Product WHERE ProductID = 1;

string sqlStatement = "DELETE FROM Product WHERE ProductID = @pid; DELETE FROM ProductStock WHERE ProductID = @pid; DELETE FROM ProductSideImage WHERE ProductID = @pid; DELETE FROM ProductImage WHERE ProductID = @pid;";

edited May 23 '17 at 11:56

Community

1
1

answered Dec 26 '13 at 17:17

Vland

4,151
2
32
43

Tag says this is mysql though. – xQbert Dec 26 '13 at 17:22
u mean create 4 line?string sqlStatement ? – Mickey Dec 26 '13 at 17:25
you can write 4 different queries or a single stored procedure. as you wish, it depends on your application anyway, on the checks you want to do etc etc – Vland Dec 26 '13 at 17:26

score 0 · Answer 2 · answered Dec 26 '13 at 17:32

Alternatively, you can do it one query, without CASCADE.

Consider the following(linking table hidden for brevity)...

 SELECT * FROM recipes;
 +-----------+-------------------------+
 | recipe_id | recipe                  |
 +-----------+-------------------------+
 |         1 | Macaroni & Cheese       |
 |         2 | Cheese on Toast         |
 |         3 | Beans on Toast          |
 |         4 | Cheese & Beans on Toast |
 |         5 | Toast & Jam             |
 |         6 | Humus                   |
 +-----------+-------------------------+

 SELECT * FROM ingredients;
 +---------------+------------+
 | ingredient_id | ingredient |
 +---------------+------------+
 |             1 | Macaroni   |
 |             2 | Cheese     |
 |             3 | Beans      |
 |             4 | Toast      |
 |             5 | Jam        |
 |             6 | Chickpeas  |
 |             7 | Tahini     |
 +---------------+------------+

 SELECT r.*
      , i.* 
   FROM recipes r 
   JOIN recipe_ingredient ri 
     ON ri.recipe_id = r.recipe_id 
   JOIN ingredients i 
     ON i.ingredient_id = ri.ingredient_id;
 +-----------+-------------------------+---------------+------------+
 | recipe_id | recipe                  | ingredient_id | ingredient |
 +-----------+-------------------------+---------------+------------+
 |         1 | Macaroni & Cheese       |             1 | Macaroni   |
 |         1 | Macaroni & Cheese       |             2 | Cheese     |
 |         2 | Cheese on Toast         |             2 | Cheese     |
 |         2 | Cheese on Toast         |             4 | Toast      |
 |         3 | Beans on Toast          |             3 | Beans      |
 |         3 | Beans on Toast          |             4 | Toast      |
 |         4 | Cheese & Beans on Toast |             2 | Cheese     |
 |         4 | Cheese & Beans on Toast |             3 | Beans      |
 |         4 | Cheese & Beans on Toast |             4 | Toast      |
 |         5 | Toast & Jam             |             4 | Toast      |
 |         5 | Toast & Jam             |             5 | Jam        |
 |         6 | Humus                   |             6 | Chickpeas  |
 |         6 | Humus                   |             7 | Tahini     |
 +-----------+-------------------------+---------------+------------+

 DELETE r
      , ri
      , i 
   FROM recipes r 
   JOIN recipe_ingredient ri 
     ON ri.recipe_id = r.recipe_id 
   JOIN ingredients i 
     ON i.ingredient_id = ri.ingredient_id 
  WHERE r.recipe = 'Humus';

 SELECT * FROM recipes;
 +-----------+-------------------------+
 | recipe_id | recipe                  |
 +-----------+-------------------------+
 |         1 | Macaroni & Cheese       |
 |         2 | Cheese on Toast         |
 |         3 | Beans on Toast          |
 |         4 | Cheese & Beans on Toast |
 |         5 | Toast & Jam             |
 +-----------+-------------------------+

 SELECT * FROM ingredients;
 +---------------+------------+
 | ingredient_id | ingredient |
 +---------------+------------+
 |             1 | Macaroni   |
 |             2 | Cheese     |
 |             3 | Beans      |
 |             4 | Toast      |
 |             5 | Jam        |
 +---------------+------------+

string sqlStatement = "DELETE FROM Product,ProductStock,ProductSideImage,ProductImage WHERE ProductID = @pid"; how to link the relationship? — Mickey, Dec 26 '13 at 17:36

how to delete productid from multiple table

2 Answers2