SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Delete From (help)

    Hi,

    I was wondering if it's possible to delete rows of tableA that's associated with tableB. Right now, I have
    Code:
    DELETE FROM choice
    WHERE choice.rel_qid = question.id AND question.rel_sid = 118
    and get "Error: 1109 - Unknown table 'question' in where clause" as the mysql error.

    Thanks
    Last edited by GeekSupport; Sep 27, 2002 at 11:48.

  2. #2
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    syntax for fully qualified field names is tablename.fieldname, not fieldname.tablename. It's looking for a question table when in reality there is no such table.

    Aaron
    Aaron Brazell
    Technosailor



  3. #3
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Sketch
    syntax for fully qualified field names is tablename.fieldname, not fieldname.tablename. It's looking for a question table when in reality there is no such table.

    Aaron
    i dont quite understand what's wrong with my syntax. choice and question are two seperate tables.

    Code:
    table        field
    -----        -----
    question     id
    question     rel_sid
    
    choice       rel_qid
    yes, i do have a question table.

    here is a screenshot of mysql-front and the error.

    Code:
    SELECT * FROM choice, question
    WHERE choice.rel_qid = question.id AND question.rel_sid = 118
    the above works fine, so it looks like i need to define/declare(?) which tables i will use? I get the same error (table not found) if i omit question. Just dunno how to do that with DELETE.
    Last edited by GeekSupport; Sep 27, 2002 at 12:31.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    check the version number of the mysql version you're running

    "using multiple tables in the DELETE statement is supported in MySQL 4.0"
    -- 6.4.6 DELETE Syntax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    my webhost is using 3.23.41

    is there any way to do this in 3.x versions?

    What i'm trying to do is this:

    Table 'choice' has a list of options that're related to the table 'question'. 'question' has the 'question.survey_id' that the admin is looking at. In order to delete the proper options from table 'choice', mysql must query table 'question' to get a list of 'question.id' that match up to the current 'question.survey_id'. At the same time, if 'choice.rel_qid' matches up with 'question.id' which is also a part of the current survey, it will delete that row in 'choice'.

    hmm..after writing out that explination, it looks like i'll have to do two seperate SQL queries: one to get the question.id's of the same survey, and another to match against choice.rel_qid (assuming there's no way to use a multi-table reference in mysql 3.24).

    hmm..now how would i do this. It doesn't look like mysql likes
    Code:
    DELETE FROM choice
    WHERE choice.rel_qid = 75, choice.rel_qid = 63
    so there goes my idea about imploding/exploding the array. i guess the only way to do it now is via foreach/while going though the first SQL statement -- not very efficient

    ----

    I found http://www.mysql.com/doc/en/Deleting...s.html#IDX1812 explaining how to delete rows from 2 related tables. Their example isn't too clear. Could someone please elaborate for mysql newbies (pref in php).

    I think I got it
    Code:
    SELECT question.id
    FROM question
    WHERE question.rel_sid = 118
    
    shows:
    58
    59
    60
    61
    62
    
    then do
    
    DELETE FROM choice
    WHERE choice.rel_qid
    IN (58, 59, 60, 61, 62)
    Last edited by GeekSupport; Sep 28, 2002 at 12:24.


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
  •