Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: i'm having one of those days!

    My brain just won't work today -
    How do i delete from my loyalty_points table based on values from my customers table please?

    I want to delete all records from the loyalty_points table where the ClientID in the Customers table =1

    SELECT dbo.loyalty_points.CustomerID, dbo.loyalty_points.LPoints, dbo.Customers.ClientID
    FROM dbo.Customers INNER JOIN
    dbo.loyalty_points ON dbo.Customers.CustomerID = dbo.loyalty_points.CustomerID

    many many thanks
    Andy

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming that ClientID and CustomerID are really the same thing, you can use:
    Code:
    DELETE FROM dbo.loyalty_points
       WHERE  1 = dbo.loyalty_points.CustomerID
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2008
    Posts
    120
    Hi Pat Phelan
    Thanks for your reply, but the clientID and customerID are not the same thing.
    The common field that links the two tables is the CustomerID

    Thanks
    Andy

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Then you need the more complex subquery solution:
    Code:
    DELETE FROM dbo.loyalty_points
       WHERE  1 = (SELECT ClientID
          FROM dbo.Customers AS b
          WHERE  b.CustomerID = loyalty_points.CustomerID)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2008
    Posts
    120
    I got it, for anyone else this may help...

    DELETE Loyalty_Points
    FROM Loyalty_Points INNER JOIN Customers
    ON Loyalty_Points.CustomerID = Customers.CustomerID
    WHERE Customers.ClientID = 1

  6. #6
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by AndyJay View Post
    I got it, for anyone else this may help...

    DELETE Loyalty_Points
    FROM Loyalty_Points INNER JOIN Customers
    ON Loyalty_Points.CustomerID = Customers.CustomerID
    WHERE Customers.ClientID = 1
    Another idea:

    Code:
    With cte as
    (
    SELECT distinct CustomerID
    FROM Customers
    WHERE clienID = 1
    )
    DELETE FROM Loyalty_Points a
    WHERE EXISTS (select * from cte where a.customerid = a.customerid)

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Bad syntax

    got it, for anyone else this may help...

    DELETE Loyalty_Points
    FROM Loyalty_Points INNER JOIN Customers
    ON Loyalty_Points.CustomerID = Customers.CustomerID
    WHERE Customers.ClientID = 1
    Please do not use 1970's Sybase dialect in T-SQL. It is like speaking Hillbilly to an English teacher.

    DELETE FROM Loyalty_Points
    WHERE EXISTS
    (SELECT *
    FROM Customers
    WHERE Loyalty_Points.customer_id = Customers.customer_id
    AND Customers.client_id = 1

  8. #8
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Celko, i appreciate your help (as i have received it in the past) but man, you need to work on your bedside manner.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Joe is kind of the House of SQL. Amazing technical skills, but not so good with social and personal skills.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Feb 2008
    Posts
    120
    :-O

    now now..............

    Have a wonderful day!

    Andy

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by AndyJay View Post
    now now..............
    Oh this is no news!

    Joe and I have talked about this at some length. He's an amazing technical resource, but his people skills are not as strong... Anyone that knows Joe can tell you this, and provide a dozen or more stories to back it up.

    One of the things that I've worked hard to ensure is that DBForums is a place where people will be civil even when they disagree. Joe has improved a lot but he tends to test me on this, regularly!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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