Page 1 of 1

delete left join?

Posted: Fri Sep 21, 2007 1:40 am
by lafflin
Hello, I was under the idea that I can delete records using the left join.
I also read that I can subsitute "delete" with "select" to ensure that I get the records I am targeting prior to actually running the delete. I would think that this implies that I should be able to change "select" to "delete" and get the desired results.
Not so in the case of trying to run a left join - delete.

Perhaps this is not possible, I don't kow I'm a newb.

here is the delete query I was running:

Code: Select all

$delete_query = 
	                   "DELETE
					              *
					    FROM 
						        student_info,
						LEFT JOIN
						        student_parent
						USING
						        (sid)
						LEFT JOIN
						         parent_info
						USING
						         (pid)
						LEFT JOIN
						         classes_students
						USING
						         (sid)
						WHERE 
						          sid = ".$result1['sid']."  ";
I am not getting any result, but a syntax error. after running the query through phpmysql I get this message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*
FROM
student_info
LEFT JOIN
st' at line 2

Posted: Fri Sep 21, 2007 3:05 am
by s.dot
I don't think the delete syntax accepts a field name or the *.

Posted: Fri Sep 21, 2007 3:14 am
by mrkite
It depends on your version of mysql.

I believe (don't take my word) that mysql 3 doesn't allow deletion on joins at all.

Later versions I believe require you to specify the table.

Code: Select all

delete table1 from table1 left join table2 on (...);
See how you must specify table1 right after "delete".

Posted: Fri Sep 21, 2007 12:05 pm
by lafflin
I'm using 4.1 and php 5. Scott was right, I needed to take the asteric out, but I still get a syntax error on the first left join.
Perhaps I should run three different queries to perform this action?

Posted: Sat Sep 22, 2007 12:40 am
by lafflin
I guess this section (DBs) isn't so very popular.
In anycase, the solution I've came up with for my issue was this:

Code: Select all

$delete_query = "
	                 DELETE
                                student_info ,
                                student_parent ,
                                parent_info ,
                                classes_students 
                      FROM
                                student_info ,
                                student_parent ,
                                parent_info ,
                                classes_students 
                      WHERE
                                student_info.sid = student_parent.sid
                      AND
                                student_info.sid = classes_students.sid
                      AND
                                student_parent.pid=parent_info.pid
                      AND 

						        student_info.sid = ".$result1['sid']."  ";

the only problem with this though is that if I didn't have a related record in any of the tables that use the PK from student_info table as a FK than the whole query fails instead of deleting the tables that do have a related record.
Anyone know a solution to this?

Posted: Sat Sep 22, 2007 9:47 am
by superdezign
Do it one query at a time, then.

Posted: Sat Sep 22, 2007 1:24 pm
by lafflin
That's the solution that I came up with, but I figured the gurus on here might know a better way. I'm a beginner so i'm always wondering if I'm doing things the right way, or the best way. What I'm learning is there often isn't a right or best way.