SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Sep 2004
    Location
    holland
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy 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 !

  2. #2
    SitePoint Guru MikeBigg's Avatar
    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

  3. #3
    SitePoint Member
    Join Date
    Sep 2004
    Location
    holland
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, that don't work...

  4. #4
    SitePoint Member
    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 !

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    which table do you wish to delete the rows from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Sep 2004
    Location
    holland
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks !


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •