delete with multiple query

[john255]

i have a query to select data in 2 rows in a database. (thanks to darchangel). Now i want to delete the records i found with this query. Is that possible in the same query ?

this is the query i use to select:

SELECT E2.id AS E2id, E2.q AS E2q, E2.a AS E2a FROM survey AS E1 INNER JOIN survey AS E2 ON E1.id = E2.id WHERE E1.a=1 AND E1.q=3

Thanks in advance !

[MikeBigg]

Not in the same query, but replace

SELECT E2.id AS E2id, E2.q AS E2q, E2.a AS E2a

with

DELETE

and you should find it works.

Mike

[john255]

No, that don't work...

[john255]

My provider let me now they gonna start with mysql 4.01 so i can use subquaries.

Hopely that solve my problem...

I like to delete the found records, but with this 'inner join' quary i can't delete the records:

SELECT E2.id AS E2id, E2.q AS E2q, E2.a AS E2a FROM survey AS E1 INNER JOIN survey AS E2 ON E1.id = E2.id WHERE E1.a='1' AND E1.q='3'

Someone nows how to solve this problem with using subqueries ?

Thanks in advance !

[r937]

which table do you wish to delete the rows from?

[john255]

Let me explain: I have a survey with 3 questions. I only want to select the 3 questions and answers from the people (id) who give 1 as answer (a) on question (q) 3.

|id |q|a|
|27|1|3|
|27|2|2|
|27|3|1|
|29|1|3|
|29|2|1|
|29|3|2|
|22|1|2|
|22|2|3|
|22|3|1|

With this query:
SELECT E2.id AS E2id, E2.q AS E2q, E2.a AS E2a FROM survey AS E1 INNER JOIN survey AS E2 ON E1.id = E2.id WHERE E1.a='1' AND E1.q='3'

i get these results:

|E2id |E2q|E2a|
|27 |1 |3 |
|27 |2 |2 |
|27 |3 |1 |
|22 |1 |2 |
|22 |2 |3 |
|22 |3 |1 |

Total 6 results, 3 results from 2 people who give 1 as answer on question 3.

So far no problem with the inner join query i'am using, but now i also want to delete this records and so far i now this is only possible by using subqueries.

[r937]

ah, sorry, i thought there were two tables

there are, but only in the sql

this is a tricky question

i tested this and it works --

delete survey
  from survey
     , survey as E1
 where survey.id = E1.id
   and E1.a=1 
   and E1.q=3

[john255]

Thanks !