How to run below SQL without using cursor
Chuan Lu asked how to run 'delete from (select first 5 * from t1) as tmp1' without a cursor. Andreas Legner showed it gives syntax error 201 and questioned the intent. Chuan said it is DB2 syntax for repeated small deletes avoiding long transactions. Andreas said it would delete only from a temporary derived table. No working Informix alternative was given.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
delete from (select first 5 * from t1) as tmp1; thanks.
delete from (select first 5 * from customer) as cust_tmp;
201: A syntax error has occurred.
Error in line 1Near character position 13
What exactly are you trying to achieve here?
From: "CHUAN LU" <luchuan@cn.ibm.com>
To: ids@iiug.org
Date: 01/25/2018 08:21 AM
Subject: How to run below SQL without using cursor [40567]
Sent by: ids-bounces@iiug.org
delete from (select first 5 * from t1) as tmp1;
thanks.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
It's DB2 syntax, it can used for delete record repeatly, and avoid long transaction.
The way I'd read this syntax is: delete (everything) from a derived table just being created and populated by this very statement - and only existing for the duration of this statement. With the 'everything' in place: what's the point in doing this? With a limited delete instead: why don't you limit the select in the first place? Anyway, nothing of your 'tmp1' table would remain after the statement finished. From: "CHUAN LU" <luchuan@cn.ibm.com> To: ids@iiug.org Date: 01/26/2018 09:38 AM Subject: Re: How to run below SQL without using cursor [40579] Sent by: ids-bounces@iiug.org It's DB2 syntax, it can used for delete record repeatly, and avoid long transaction. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.