SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Dec 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble deleting records in MYSQL

    Hi there folks

    I am a novice with SQL and can't figure out how to delete records in my MYSQL database.

    I use this code to select the records without any problems but don't know how I can then delete these same records from the database.

    SELECT *
    FROM `amember_members` , `amember_payments`
    WHERE amember_members.member_id = amember_payments.member_id
    AND amember_members.status = '0'

    When I change SELECT to DELETE it comes up with an error message.

    I am doing this in MYPHP and am using version 5

    Thanks in advance

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by milliondollarbab View Post
    When I change SELECT to DELETE it comes up with an error message.
    What error?

  3. #3
    SitePoint Member
    Join Date
    Dec 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Error
    SQL query:

    DELETE * FROM `amember_members` ,
    `amember_payments` WHERE amember_members.member_id = amember_payments.member_id AND amember_members.status = '0'

    MySQL said:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*
    FROM `amember_members` , `amember_payments`
    WHERE amember_members.member_i' at line 1

  4. #4
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    because you dont need the *
    remove it and it will delete all the entries related to that result

    http://dev.mysql.com/doc/refman/5.0/en/delete.html
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  5. #5
    SitePoint Member
    Join Date
    Dec 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I used the suggested code

    DELETE
    FROM `amember_members` , `amember_payments`
    WHERE amember_members.member_id = amember_payments.member_id
    AND amember_members.status = '0

    And now I get the following error.

    Error
    SQL query:

    DELETE FROM `amember_members` ,
    `amember_payments` WHERE amember_members.member_id = amember_payments.member_id AND amember_members.status = '0'

    MySQL said:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE amember_members.member_id = amember_payments.member_id
    AND amember_member' at line 3

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    according to da manual, your choices for multi-table DELETEs are as follows:

    DELETE tbl_name [, tbl_name ...]
    FROM table_references
    [WHERE where_definition]

    or --

    DELETE FROM tbl_name [, tbl_name ...]
    USING table_references
    [WHERE where_definition]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Dec 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    according to da manual, your choices for multi-table DELETEs are as follows:

    DELETE tbl_name [, tbl_name ...]
    FROM table_references
    [WHERE where_definition]

    or --

    DELETE FROM tbl_name [, tbl_name ...]
    USING table_references
    [WHERE where_definition]
    So based on what you have outlined above how can I delete only the records that I can select using the below syntax? Sorry but I am an absolute beginner and don't want to lose wanted data.

    SELECT *
    FROM `amember_members` , `amember_payments`
    WHERE amember_members.member_id = amember_payments.member_id
    AND amember_members.status = '0

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    which rows, from which table(s), do you want to delete?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Dec 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT *
    FROM `amember_members` , `amember_payments`
    WHERE amember_members.member_id = amember_payments.member_id
    AND amember_members.status = '0

    When I use the about query it selects 1,300 members that I would like to delete.

    So basically I'd like to delete all the records that the above query generates.

    Thanks in advance.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i don't think you understood the question

    first of all, why do you need the payments table in your SELECT?

    now, once you've answered that, do you want to delete payments as well as members?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Dec 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just checked the members table and it does include the status field so I guess all I really want to do is delete all the members that have a status of 0.

    Sorry for the confusion.

    Thanks again.


Bookmarks

Posting Permissions

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