delete left join?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

delete left join?

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I don't think the delete syntax accepts a field name or the *.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
mrkite
Forum Contributor
Posts: 104
Joined: Tue Sep 11, 2007 4:19 am

Post 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".
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

Post 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?
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

Post 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?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Do it one query at a time, then.
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

Post 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.
Post Reply