Hi,

I get an error if I execute this:

DELETE * FROM invoice JOIN user ON user.id = invoice.FK_user WHERE invoice.id=51 AND user.id=1

"#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 invoice JOIN user ON user.id = invoice.FK_user WHERE invoice.id =51 AND u' at line 1"

To test I do this:

SELECT * FROM invoice JOIN user ON user.id = invoice.FK_user WHERE invoice.id=51 AND user.id=1

...and I get the 1 record I want to delete

Anybody sees the syntax error in the delete statement?

btw - DELETE FROM ... and DELETE * FROM give the same error result.

I use MySQL version: 5.0.8

Thanks,

Marc

On 13-06-2011 13:24, marc wrote:
no, they dont, it shoudl be DELETE FROM .....
not DELETE * FROM .....

you need USING somewhere
(http://dev.mysql.com/doc/refman/5.0/en/delete.html)

--
Luuk

El 13/06/2011 13:24, marc escribió/wrote:
The error message tells you the exact location of the error: the
asterisk. You can't specify columns in a DELETE clause since you can
only remove the complete row.

Not in my server... In the first query, it complains about the JOIN.

See the multi-table syntax at:

http://dev.mysql.com/doc/refman/5.0/en/delete.html

You have to provide a table name right after the DELETE clause:

DELETE invoice
FROM invoice
JOIN user ON user.id = invoice.FK_user
WHERE invoice.id=51 AND user.id=1

(Code is not tested.)

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On Mon, 13 Jun 2011 04:24:42 -0700 (PDT), marc wrote:

http://dev.mysql.com/doc/refman/5.0/en/delete.html doesn't show the
DELETE * FROM as being acceptable.

You haven't told it properly which tables to delete from under the
condition of using the JOIN ... ON syntax. Presuming you only want to
delete from invoice, try the form

DELETE invoice FROM invoice JOIN user ON ...

On the off-chance that you want to delete from both invoice and user,
try

DELETE invoice, user FROM invoice JOIN user ON ...

--
74. When I create a multimedia presentation of my plan designed so that
my five-year-old advisor can easily understand the details, I will
not label the disk "Project Overlord" and leave it lying on top of
my desk. --Peter Anspach's list of things to do as an Evil Overlord

On Jun 13, 1:20*pm, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:

I prefer the DELETE FROM ... USING ... form as it says more accurately
what I want to do.