3 Replies - 196 Views - Last Post: 25 September 2017 - 07:44 PM

#1 dongtrien  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 17-June 15

SQL Server delete data with linked Table ?

Posted 24 September 2017 - 07:20 PM

Suppose you have 3 tables: TABHDBH, TABHDBHCHUYEN and TABHDBHCT
Table TABHDBH has the following fields:
NGAY DateTime;
IDHDBH int
...

Table IDHDBHCT has the following fields:
MAHDBH int
...

TABHDBHCHUYEN
NGAYCHUYEN datetime
MAHDBH int
...

RELATIONSHIP OF 3 TABLE
TABHDBHCHUYEN.MAHDBH head n
TABHDBH.IDHDBH head 1
TABHDBHCT.MAHDBH head n
DELETE T1
FROM TABHDBHCHUYEN T1
INNER JOIN TABHDBH T2 ON T1.MAHDBH = T2.IDHDBH INNER JOIN
           TABHDBHCT T3 ON T2.IDHDBH = T3.MAHDBH
WHERE (CONVERT(VARCHAR, NGAYCHUYEN, 101) <= '01/07/2017')


When running the query without error only delete the 1 record in TABHDBHCHUYEN and the records in the other 2 TABLE not delete record, you see help me wrong? Created a relational table for 3 tables to Delete Rule mode to Cascade but not delete.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Server delete data with linked Table ?

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 5078
  • View blog
  • Posts: 13,706
  • Joined: 18-April 07

Re: SQL Server delete data with linked Table ?

Posted 24 September 2017 - 07:39 PM

Yes the delete is only going to affect the main table, not your joined tables just because they are joined. If TABHDBH and TABHDBHCT are tied into the other table through their keys, then you need to setup a cascade delete on the other tables so that when the primary record is deleted, it will delete their related records automatically. :)
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,904
  • Joined: 12-December 12

Re: SQL Server delete data with linked Table ?

Posted 24 September 2017 - 11:30 PM

If you don't want to cascade the delete then you will need to first delete records from the related tables before removing the main record(s).

Also, if your code allows you to delete records from a main table, without first removing them from related tables, then this needs to be fixed first by applying foreign key constraints. It should not be possible to remove a main record whilst related records remain, fundamentally breaking the integrity of the data.



Also consider that it is unusual to delete records; at least, without first archiving them to a backup table or database - leaving an audit trail/history.
Was This Post Helpful? 0
  • +
  • -

#4 dongtrien  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 17-June 15

Re: SQL Server delete data with linked Table ?

Posted 25 September 2017 - 07:44 PM

In relationship. I got into the INSERT AND UPDATE Specification of the foreign key that set Delete Rule to Cascade but it still does not delete.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1