SitePoint Sponsor |
|
User Tag List
Results 1 to 8 of 8
Thread: delete with multiple query
-
Jan 15, 2005, 08:22 #1
- Join Date
- Sep 2004
- Location
- holland
- Posts
- 9
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
delete with multiple query
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 !
-
Jan 15, 2005, 12:42 #2
- Join Date
- Jun 2004
- Location
- Reading, UK
- Posts
- 970
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Jan 16, 2005, 04:56 #3
- Join Date
- Sep 2004
- Location
- holland
- Posts
- 9
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
No, that don't work...
-
Jan 16, 2005, 05:12 #4
- Join Date
- Sep 2004
- Location
- holland
- Posts
- 9
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
subqueries
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 !
-
Jan 16, 2005, 06:08 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
which table do you wish to delete the rows from?
-
Jan 16, 2005, 06:57 #6
- Join Date
- Sep 2004
- Location
- holland
- Posts
- 9
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
subqueries
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.
-
Jan 16, 2005, 07:32 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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 --Code:delete survey from survey , survey as E1 where survey.id = E1.id and E1.a=1 and E1.q=3
-
Jan 16, 2005, 08:27 #8
- Join Date
- Sep 2004
- Location
- holland
- Posts
- 9
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks !
Bookmarks