Delete record from multiple tables in mysql

Question

0.00/5 (No votes)

See more:

I have two tables, "Ledgers" & "Bank_Detls", both have a field "Cust_ID" which is a primary key for both tables. Ledgers holds basic information about a party and Bank_Detls holds banking information of that party. Now when I want to delete any record from Ledgers I want the record from Bank_Detls attached to that record to also get deleted. Below is the code

sqlcommand.CommandText = "DELETE Ledgers,Bank_Detls FROM Ledgers INNER JOIN Bank_Detls ON Bank_Detls.Cust_ID = Ledgers.Cust_ID WHERE Ledgers.Cust_ID = @custid and Bank_Detls.Cust_ID = @custid"

            sqlcommand.Parameters.Clear()
            sqlcommand.Parameters.AddWithValue("custid", TxtCustID.Text)
            sqlcommand.ExecuteNonQuery()

If suppose a party has Bank information in Bank_Detls table then above code works perfectly, but if the party has no Bank information in Bank_Detls table the record from Ledgers is also not deleted. What am I doing wrong?

What I have tried:

I my previous post I have told that I am new to programming. Frankly speaking I have not tried much on this issue except trying to get help from internet.

Posted 28-Nov-19 1:04am

Member 13550326

Updated 28-Nov-19 2:43am

Add a Solution

Comments

Visweswaran N 28-Nov-19 7:24am

can you give me the table informations?

Afzaal Ahmad Zeeshan 28-Nov-19 7:53am

I would highly recommend using SQL relations and perform a delete on child tables automatically as the parent data gets removed. That is the benefit of using a SQL database.

2 solutions

Add a Solution

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

Richard Deeming · Answer 1 · 2019-11-28T02:43:00

A DELETE statement cannot target more than one table.

You will either need to issue two DELETE statements:

C#

sqlcommand.CommandText = "DELETE FROM Bank_Detls WHERE Cust_ID = @custid; DELETE FROM Ledgers WHERE Cust_ID = @custid;"

sqlcommand.Parameters.Clear()
sqlcommand.Parameters.AddWithValue("@custid", TxtCustID.Text)
sqlcommand.ExecuteNonQuery()

or change your foreign key constraint to cascade deletes:
Cascading Referential Integrity | Primary and Foreign Key Constraints - SQL Server | Microsoft Docs[^]

phil.o · Answer 2 · 2019-11-28T01:08:00

Solution 1

Please try to replace your INNER JOIN with a LEFT JOIN, and see what happens.
Otherwise you can also issue two distinct DELETE clauses, one for each table.

Posted 28-Nov-19 1:08am

phil.o

Delete record from multiple tables in mysql

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Delete record from multiple tables in mysql

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us