Help with join, delete

[Mike]

I am not a sql person and could use some help with a delete...here is
what I want:

I have the following tables/fields (only including necessary fields)

answers
result_id

results
result_id
points

I want to delete the records for result_id from both tables when
points in the results table <50

Thanks in advance for any assistance.

Mike

[Aggro]

Mike wrote:

I have the following tables/fields (only including necessary fields)

answers
result_id

results
result_id
points

I want to delete the records for result_id from both tables when
points in the results table <50

This might help:
http://dev.mysql.com/doc/mysql/en/DELETE.html

But please note the part saying:
"From MySQL 4.0, you can specify multiple tables in the DELETE statement
to delete rows from one or more tables depending on a particular
condition in multiple tables. However, you cannot use ORDER BY or LIMIT
in a multiple-table DELETE.

The first multiple-table DELETE syntax is supported starting from MySQL
4.0.0. The second is supported starting from MySQL 4.0.2. The
table_references part lists the tables involved in the join. Its syntax
is described in section 14.1.7.1 JOIN Syntax. "

[Mike]

It help a little...But I still need some guidance.

This selects what I want:
SELECT * FROM answers, results WHERE
answers.result_id=results.result_id AND results.score <50

so I would think this would delete it:
DELETE FROM answers, results WHERE answers.result_id=results.result_id
AND results.score <50

but it doesn't, can anyone shed some light on it?

Thanks Mike

Aggro <sp**********@yahoo.com> wrote in message news:<3a***************@read3.inet.fi>...

Mike wrote:

I have the following tables/fields (only including necessary fields)

answers
result_id

results
result_id
points

I want to delete the records for result_id from both tables when
points in the results table <50

This might help:
http://dev.mysql.com/doc/mysql/en/DELETE.html

But please note the part saying:
"From MySQL 4.0, you can specify multiple tables in the DELETE statement
to delete rows from one or more tables depending on a particular
condition in multiple tables. However, you cannot use ORDER BY or LIMIT
in a multiple-table DELETE.

The first multiple-table DELETE syntax is supported starting from MySQL
4.0.0. The second is supported starting from MySQL 4.0.2. The
table_references part lists the tables involved in the join. Its syntax
is described in section 14.1.7.1 JOIN Syntax. "

[Aggro]

Mike wrote:

so I would think this would delete it:
DELETE FROM answers, results WHERE answers.result_id=results.result_id
AND results.score <50

but it doesn't, can anyone shed some light on it?

What version of MySQL server you are using?

[Mike]

It help a little...But I still need some guidance.

This selects what I want:
SELECT * FROM answers, results WHERE
answers.result_id=results.result_id AND results.score <50

so I would think this would delete it:
DELETE FROM answers, results WHERE answers.result_id=results.result_id
AND results.score <50

but it doesn't, can anyone shed some light on it?

Thanks Mike

Aggro <sp**********@yahoo.com> wrote in message news:<3a***************@read3.inet.fi>...

Mike wrote:

I have the following tables/fields (only including necessary fields)

answers
result_id

results
result_id
points

I want to delete the records for result_id from both tables when
points in the results table <50

This might help:
http://dev.mysql.com/doc/mysql/en/DELETE.html

But please note the part saying:
"From MySQL 4.0, you can specify multiple tables in the DELETE statement
to delete rows from one or more tables depending on a particular
condition in multiple tables. However, you cannot use ORDER BY or LIMIT
in a multiple-table DELETE.

The first multiple-table DELETE syntax is supported starting from MySQL
4.0.0. The second is supported starting from MySQL 4.0.2. The
table_references part lists the tables involved in the join. Its syntax
is described in section 14.1.7.1 JOIN Syntax. "

[Aggro]

Mike wrote:

so I would think this would delete it:
DELETE FROM answers, results WHERE answers.result_id=results.result_id
AND results.score <50

but it doesn't, can anyone shed some light on it?

What version of MySQL server you are using?