Informix Forum - Read-only Archive

Re: sql delete bypass where question

Posted By: ART KAGEL, BLOOMBERG/ 731 LEXIN
Date: Wednesday, 5 April 2006, at 11:29 a.m.

In Response To: sql delete bypass where question (MICHAEL CLARK)

Don't use the interactive menu mode in dbaccess for things like this. That mode
is checking the statement type and when it sees you have issued an update or
delete without a WHERE clause it asks the question to make sure you know what
you are about to do will affect every row. You can use the commandline mode of
dbaccess:

dbaccess <database> -
> delete from table_name;
>

HOWEVER, it is a VERY bad idea to use a delete statement to clear out a table in
a single swoop. It uses lots of locks and other resources and can seriously
affect other users. Better to do this in smaller chunks (faster too!) You can
use by dbdelete utility which is contained in the package utils2_ak available
for download from the IIUG Software Repository. Dbdelete will delete 8192 rows
in a single transaction and commit before processing the next batch of records.
It will complete at least as fast as the 'delete from tablename' but will use
far fewer resources.

Art S. Kagel

----- Original Message -----
From: Michael Clark <informix-forum@iiug.org>
At: 4/ 5 11:25

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]*';

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

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• sql delete bypass where question
  MICHAEL CLARK -- Tuesday, 4 April 2006, at 2:41 p.m.
  • Re: sql delete bypass where question
    MICHAEL CLARK -- Tuesday, 4 April 2006, at 3:33 p.m.
  • Re: sql delete bypass where question
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Wednesday, 5 April 2006, at 11:29 a.m.
  • Re: sql delete bypass where question
    Peter_Logan@spartanstores.com -- Wednesday, 5 April 2006, at 11:34 a.m.
  • Re: sql delete bypass where question
    Obnoxio The Clown -- Wednesday, 5 April 2006, at 1:50 p.m.

Informix Forum - Read-only Archive is maintained by Administrator with WebBBS 5.12.