Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: 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:

    Code:
    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?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have you defined foreign key relationships?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Darnit Poots, you beat me to it again!
    I was going to ask for the joins between the tables :'(
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    joins <> relationships
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *shifty look*
    I knew that
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2005
    Posts
    240
    Quote Originally Posted by pootle flump
    Have you defined foreign key relationships?
    Nope, makes sense to do that I suppose.

    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?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by KevCB226
    Can I do this by creating a diagram?
    Yeah - but script it out.
    Basic sample code. Check BoL for more options.
    Code:
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •