Deleting Records

[Aleks]

Hi,

I need to delete records from my database, I have 3 tables

Table 1 has:

Users
1> UserId
2> CaseId

Here I have no problem, because I am deleting the user, so I use Delete from
table1 where userid = 1, that will delete the user from the table, the
problem is in table # 2

Cases
1> Id
2> CaseId

Here I don't have the UserId, but I have the CaseId, which comes from table
1 and can be linked.

How can I delete all 'cases' in table 2 where cases.caseid = users.caseid in
table 1 and users.userid in table 1 = 1 ?

In other words how can I delete all cases from table 2 where user = 1 ?

Thanks in advance,

Aleks

 

[Norman Yuan]

You are talking SQL Server/MDSE, aren't you?

Two ways, at least.

1. Turn on "Cascade delete related records" when create relationship bewteen
the two tables (when you establish relationship between tables in ADP
project's dialgram, a dialog box shows, allowing you to check a checkbox for
this; or in the diagram, you can right-click an existing relationship and
select properties, then select "Relationships" tab).

2. run multiple deleting: delete records in child table first, then the
record(s) in parent table:

DELETE FROM tblCases WHERE CaseID IN (SELECT CaseID FROM tblUsers WHERE
UserID=1)
DELETE FROM tblUsers WHERE UserID=1

You may want to wrap the multiple deleting statement in a transaction
(either in your application or in SQL Server), in case the parenet record
deleting fails, you can get deleted child records back.

 

[Aleks]

thx, ill try the second one using a stored procedure.

Aleks