Trouble deleting records in MYSQL

[milliondollarbab]

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

[guido2004]

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

What error?

[milliondollarbab]

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

[spikeZ]

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

[milliondollarbab]

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

[r937]

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]

[milliondollarbab]

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

[r937]

which rows, from which table(s), do you want to delete?

[milliondollarbab]

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.

[r937]

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?

[milliondollarbab]

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.