SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: Delete From (help)
-
Sep 27, 2002, 11:46 #1
- 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
ThanksLast edited by GeekSupport; Sep 27, 2002 at 11:48.
-
Sep 27, 2002, 11:56 #2
- 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
-
Sep 27, 2002, 12:07 #3
- 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
Code:table field ----- ----- question id question rel_sid choice rel_qid
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
Last edited by GeekSupport; Sep 27, 2002 at 12:31.
-
Sep 27, 2002, 16:22 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Sep 27, 2002, 16:32 #5
- 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
----
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