Help with ServerAgent Job syntax

Hi,

Hopefully someone can help me. I'm having difficulty with the syntax to delete a record from 4 joined tables when creating a job.

I have an 'Applicants' table linked to four other tables 'Courses', 'EmploymentHistory', 'Qualifications', and 'References' using the field 'ApplicantID'.

I want to create a job to delete all the records where the Finalised field = '0' and the record was created more than 3 days ago.

The syntax I have been using on just one of the joined tables to start with doesn't delete from the joined table:

USE OnlineApplications
DELETE Applicants
FROM Applicants
INNER JOIN Courses
ON Applicants.ApplicantID = Courses.ApplicantID
WHERE Finalised = 0 AND Created < DATEADD(d, 3, Created)

How can I delete the records from the other four tables?

ThanksHave you defined foreign key relationships?|||Darnit Poots, you beat me to it again!
I was going to ask for the joins between the tables :'(|||joins <> relationships ;)|||*shifty look*
I knew that ;)|||Have you defined foreign key relationships?

Nope, makes sense to do that I suppose. :o

For the projects I do, I merely create the tables then use the tables to store the data, haven't needed to create relationships in the past, bad development I know.

But I haven't really looked into the features of SQL Server yet.

I take it the syntax should work then if I create relationships?
Can I do this by creating a diagram?
Then I take it I need to set the joins to cascade to delete from other tables?|||Can I do this by creating a diagram?Yeah - but script it out.
Basic sample code. Check BoL for more options.

IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N't_name') BEGIN
DROP TABLE t_name
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'other_t_name') BEGIN
DROP TABLE other_t_name
END
CREATE TABLE dbo.other_t_name
(
c_name INT NOT NULL CONSTRAINT df_other_t_name_c_name DEFAULT 0
, CONSTRAINT pk_other_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_other_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_other_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
)
GO
CREATE TABLE dbo.t_name
(
c_name INT NOT NULL CONSTRAINT df_t_name_c_name DEFAULT 0
, CONSTRAINT pk_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
, CONSTRAINT fk_t_name_other_t_name FOREIGN KEY (c_name) REFERENCES other_t_name (c_name) ON DELETE CASCADE
)
GO

HTH

Help with query

Hi, I would like some help with a query. I would like to delete all
rows when the key appears in one table and not the other. For example
the info in table 1 on my DB is my current work. I then import the upto
date info monthly into table 2. Currently I have to manually delete the
info from table 2, hence the need for the query to mismatch and leave
me with the current info.
Hope this makes sense
Thanks)It's ususally a good idea to start a new thread so that your request for he
lp doesn't get lost in someone else's responses. Also good to provide table
DDL and sample data in the form of INSERT statements.)
One way to do this is:
DELETE Table2
WHERE Table2.Key IN ( SELECT Table1.Key
FROM Table1
WHERE {criteria in Table1}
)
Or vice versa to delete from Table1
Let me know if this helps.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"John" <john_sarah_kerr@.hotmail.co.uk> wrote in message news:1151180034.679815.100870@.b68g2
000cwa.googlegroups.com...
> Hi, I would like some help with a query. I would like to delete all
> rows when the key appears in one table and not the other. For example
> the info in table 1 on my DB is my current work. I then import the upto
> date info monthly into table 2. Currently I have to manually delete the
> info from table 2, hence the need for the query to mismatch and leave
> me with the current info.
>
> Hope this makes sense
>
> Thanks
>