Gossamer Forum
Home : General : Databases and SQL :

Help with Delete Query

Quote Reply
Help with Delete Query
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
Quote Reply
Re: [Omni] Help with Delete Query In reply to
See the mysql syntax page:

http://www.mysql.com/doc/en/DELETE.html
Quote Reply
Re: [Omni] Help with Delete Query In reply to
Why not just do it in 2 queries? Unsure I'm not sure if DELETE supports multiple deletes in one go.. (with multiple tables).

Could be wrong though Unimpressed

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Help with Delete Query In reply to
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.
Quote Reply
Re: [Paul] Help with Delete Query In reply to
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?
Quote Reply
Re: [Omni] Help with Delete Query In reply to
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.