error using an external table as part of a delete

Hi All, I have two tables, gwk_lng_rng_sql is a regular informix database table and gwk_lng_rng_sql_ext is an external table. I am trying to delete any rows in the regular informix table that do not exist in the external table. Each of the three methods provided below return an error when I run them in Server Studio. merge into gwk_lng_rng_sql USING gwk_lng_rng_sql_ext on gwk_lng_rng_sql.address = gwk_lng_rng_sql_ext.address WHEN NOT MATCHED THEN DELETE; SQL Error (-201): A syntax error has occurred. delete gwk_lng_rng_sql where not exists(select 1 from gwk_lng_rng_sql_ext where gwk_lng_rng_sql_ext.address = gwk_lng_rng_sql.address); SQL Error (-26214): delete gwk_lng_rng_sql where gwk_lng_rng_sql.address not in(select gwk_lng_rng_sql_ext.address from gwk_lng_rng_sql_ext); SQL Error (-26214): Has anyone seen this before? Is there a way to do it?

The 26214 error description indicates that an external table cannot be used in a subquery or outer join, so that explains #2 and #3. That's a shame. For #1, the syntax of the MERGE statement shows that you can only use "WHEN MATCHED THEN DELETE". Using "WHEN NOT MATCHED..." can only be followed by "THEN INSERT", which will insert into the target table...not what you want. Without loading the external table into a table within the database, I can't think of many options. One ugly way would be to do an outer join between the tables into a temp table, and then get the values that didn't join, but I bet there's a much easier way that I can't think of right now... Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JOHN HENRY Sent: Tuesday, August 29, 2017 9:52 AM To: ids@iiug.org Subject: error using an external table as part of a delete [39784] Hi All, I have two tables, gwk_lng_rng_sql is a regular informix database table and gwk_lng_rng_sql_ext is an external table. I am trying to delete any rows in the regular informix table that do not exist in the external table. Each of the three methods provided below return an error when I run them in Server Studio. merge into gwk_lng_rng_sql USING gwk_lng_rng_sql_ext on gwk_lng_rng_sql.address = gwk_lng_rng_sql_ext.address WHEN NOT MATCHED THEN DELETE; SQL Error (-201): A syntax error has occurred. delete gwk_lng_rng_sql where not exists(select 1 from gwk_lng_rng_sql_ext where gwk_lng_rng_sql_ext.address = gwk_lng_rng_sql.address); SQL Error (-26214): delete gwk_lng_rng_sql where gwk_lng_rng_sql.address not in(select gwk_lng_rng_sql_ext.address from gwk_lng_rng_sql_ext); SQL Error (-26214): Has anyone seen this before? Is there a way to do it? **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.