Informix Forum

Re: #107: sql delete bypass where question

Posted By: Jonathan Leffler
Date: Thursday, 6 April 2006, at 12:18 a.m.

On 4/4/06, informix-forum@iiug.org <informix-forum@iiug.org> wrote:
> #107: sql delete bypass where question (MICHAEL CLARK)
> #108: Re: sql delete bypass where question (MICHAEL CLARK)

> MESSAGE: (#107) sql delete bypass where question
> <http://www.iiug.org/forums/informix-forum/index.cgi?rev=107>
> AUTHOR: MICHAEL CLARK
> DATE: Tuesday, 4 April 2006, at 2:41 p.m.
>
> I'd like to automate deletion of all the data in an entire table. How do I
> bypass the question when there is no where clause in the delete statement?
>
> "No where clause on Delete or Update, every row in table will be affected"
> I've tried using
>
> delete from table_name
> where upper(column_name) matches '[A-Z]*';

The conventional condition is WHERE 1 = 1 - which is cheaper than a
function call and a MATCHES operation because the optimizer recognizes
it as a special case (but doesn't whinge that you didn't add a WHERE
clause).

Alternatively, get SQLCMD from the IIUG archives - it assumes you know
what you're doing and doesn't pester you with silly questions like
"Do you know what you're doing?"

> but it doesn't work even though using this syntax in a select statement
> retrieves all the rows.

[...message 108 explained what the problem was...]

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Informix Forum is maintained by Administrator with WebBBS 5.12.