Delete From (help)

[GeekSupport]

Hi,

I was wondering if it's possible to delete rows of tableA that's associated with tableB. Right now, I have

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

[Technosailor]

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

[GeekSupport]

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.

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.

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.

[r937]

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

[GeekSupport]

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

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

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)