469,322 Members | 1,606 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,322 developers. It's quick & easy.

Help with join, delete

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
Jul 20 '05 #1
5 2617
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. "
Jul 20 '05 #2
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. "

Jul 20 '05 #3
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?
Jul 20 '05 #4
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. "

Jul 20 '05 #5
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?
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Mike | last post: by
28 posts views Thread by stu_gots | last post: by
1 post views Thread by Rahul | last post: by
3 posts views Thread by Matthew Warren | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.