[ic] Re: how to remove records from database

[Mark Johnson]

Russ Riggs wrote:
> 
> That is the correct syntax.  You can use either LIKE or MATCHES
> and you can use either the '%' or the '*'....assuming, of course, that
> mysql is ansi compliant.  So you should be able to mass delete with
> either
> 
> delete from merchandising where sku matches 'os*';
> 
> or
> 
> delete from merchandising where sku like 'os%';

I have never seen matches or * used in this context. The 'matches'
operator does have a function in mysql regarding a table that has been
full-text indexed, but I believe this is mysql-specific. In any case,
the matches and * do not work in mysql or oracle.

> 
> Now to a question I have.  I prefer to handle the maintenance of the
> database thru the admin UI as opposed to mucking directly with the
> database.  I deleted all the items from the database via the 'items'
> editor an assumed, erroneously, that all records that are related to
> those items (processing too) would be handled...ie., any order,
> specials, promos, etc., would be automatically dealt with.  Can someone
> tell me a quick and dirty way to zero out the database so that I may
> add my own merchandise so I can see what this thing looks like?

There is no feature of the UI admin to support this sort of cascading
delete. It would be, in my estimation, imprudent to make this type of
data deletion a default behavior. You must also remember that the UI is
designed to support the utilization of GDBM files, in which there exist
no such concept as a foreign key.

You can define your own form action which could cascade through the
tables of interest and delete all occurrences of related data, but the
management of that relation, and on which tables you wish to have the
cascading delete occur, would be entirely up to you to define and
maintain. For that matter, you could perform the same feature in a
mv_click, or in an AutoLoad global sub, but you still will have to
define and maintain the relations to be enforced.

In the case of Oracle (and probably other databases that enforce
foreign-key constraints), you can define your tables in such a manner so
as to have this functionality occur independent of Interchange or any
other application that uses the data. If you define all your FK
constraints with the ON DELETE CASCADE option, what you desire will
happen automatically.


-- 
Mark Johnson
Senior Systems Architect - Professional Services
Red Hat, Inc.
E-Business Solutions
markj@redhat.com
703-456-2912