Can someone tell me why this statement doesn't work:

DELETE

FROM OE_HDR, OE_HDR_no_lines

WHERE OE_HDR.KEY_FLD = OE_HDR_no_lines.KEY_FLD

I am trying to delete records from the first table (OE_HDR) where those same records exist in the second table (OE_HDR_no_lines). If I change the "DELETE" to "SELECT *" I get the records I want to delete.

thanx

See the mysql syntax page:

http://www.mysql.com/doc/en/DELETE.html

Why not just do it in 2 queries? I'm not sure if DELETE supports multiple deletes in one go.. (with multiple tables).

Could be wrong though

Andy (mod)
andy@ultranerds.co.uk

---

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates

Andy, Thanx for the response. I am not trying to delete from both tables I am only trying to delete records from the first table using matching records in the second table.

PAul, Thanx for the reply.

I checked the link you gave me and changed the format of my query to:

DELETE t1,t2 FROM t1 WHERE t1.id=t2.id

where t1 is the table containing the records I wish to delete and t2 contains copies of records I wish to delete from t1.

I am getting an error "Unrecodnized table or correlation name".

Do I need to Join these two tables some how first?

Are you using Mysql 4+? If not, it doesn't support using more then one table in the from clause. Do a 'SELECT VERSION()' to find out.

Cheers,

Alex
--
Gossamer Threads Inc.