Delete with Outer Joins

Trying to find syntax which will delete records from table B which do not meet certain criteria in table A Can use Outer Joins in a select to build an array then loop array and delete records from DB. Replacing SELECT with DELETE results in syntax error The USING clause also does not work with Informix. Following also produces syntax error on USING DELETE FROM lineitem USING order o, lineitem l WHERE o.qty < 1 AND o.order_num= l.order_num Hoping to find a single sql to do this. Using Informix 11.7 Hope someone can help Derrick Muller

delete from table where not exists (select 0 from <your subquery here> AND <subquery.key>=3Dtable.key) The first part of the subquery is your conditions you want to use, the = second part links the subquery to the main table you're trying to clean = up. Be careful with this. j. On Jun 4, 2012, at 2:24 PM, DERRICK MULLER wrote: > Trying to find syntax which will delete records from table B which do = not meet=20 > certain criteria in table A=20 >=20 > Can use Outer Joins in a select to build an array then loop array and = delete=20 > records from DB.=20 >=20 > Replacing SELECT with DELETE results in syntax error=20 >=20 > The USING clause also does not work with Informix. Following also = produces=20 > syntax error on USING=20 >=20 > DELETE FROM lineitem USING order o, lineitem l WHERE o.qty < 1 AND =o.order_num=20 > =3D l.order_num=20 >=20 > Hoping to find a single sql to do this.=20 >=20 > Using Informix 11.7=20 >=20 > Hope someone can help=20 >=20 > Derrick Muller=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

delete from lineitem as l where l.order_num in (select o.order_num from orders where o.qty < 1); Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jun 4, 2012 at 2:24 PM, DERRICK MULLER <derrick@xact.co.za> wrote: > Trying to find syntax which will delete records from table B which do not > meet > certain criteria in table A > > Can use Outer Joins in a select to build an array then loop array and > delete > records from DB. > > Replacing SELECT with DELETE results in syntax error > > The USING clause also does not work with Informix. Following also produces > syntax error on USING > > DELETE FROM lineitem USING order o, lineitem l WHERE o.qty < 1 AND> o.order_num > = l.order_num > > Hoping to find a single sql to do this. > > Using Informix 11.7 > > Hope someone can help > > Derrick Muller > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8fb2081c5f490f04c1aaf681

The MERGE statement could not help? I use it in similar situation with temp tables, but to update... very fast, always better ans easly what use sub-querys.... http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids _sqs_2030.htm On 4/6/2012 16:58, Art Kagel wrote: > delete from lineitem as l > where l.order_num in (select o.order_num from orders where o.qty< 1);> > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Mon, Jun 4, 2012 at 2:24 PM, DERRICK MULLER<derrick@xact.co.za> wrote: > >> Trying to find syntax which will delete records from table B which do not >> meet >> certain criteria in table A >> >> Can use Outer Joins in a select to build an array then loop array and >> delete >> records from DB. >> >> Replacing SELECT with DELETE results in syntax error >> >> The USING clause also does not work with Informix. Following also produces >> syntax error on USING >> >> DELETE FROM lineitem USING order o, lineitem l WHERE o.qty< 1 AND>> o.order_num >> = l.order_num >> >> Hoping to find a single sql to do this. >> >> Using Informix 11.7 >> >> Hope someone can help >> >> Derrick Muller >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --e89a8fb2081c5f490f04c1aaf681 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks Art How would you deal with say 2 fields you need to match up and not just one. In your example delete from lineitem as l where l.order_num in (select o.order_num from orders where o.qty < 1); you had l.order_num and l.order_stock_code to match up. Derrick Muller

Concatenation. It's not fun, but it works. Art On Jun 5, 2012 1:18 PM, "DERRICK MULLER" <derrick@xact.co.za> wrote: > Thanks Art > > How would you deal with say 2 fields you need to match up and not just one. > > In your example > > delete from lineitem as l > where l.order_num in (select o.order_num from orders where o.qty < 1);> > you had l.order_num and l.order_stock_code to match up. > > Derrick Muller > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93406bbaed36c04c1bce79e

That's one of the reasons why the MERGE can be a good idea as Cesar mentioned. Concatenation (or ROW()) can work but will disable index usage. Regards. On Tue, Jun 5, 2012 at 6:23 PM, Art Kagel <art.kagel@gmail.com> wrote: > Concatenation. It's not fun, but it works. > > Art > On Jun 5, 2012 1:18 PM, "DERRICK MULLER" <derrick@xact.co.za> wrote: > > > Thanks Art > > > > How would you deal with say 2 fields you need to match up and not just > one. > > > > In your example > > > > delete from lineitem as l > > where l.order_num in (select o.order_num from orders where o.qty < 1);> > > > you had l.order_num and l.order_stock_code to match up. > > > > Derrick Muller > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --14dae93406bbaed36c04c1bce79e > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf3074b152006f5f04c1c01a11