On 26 Oct, 15:23, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Fri, 26 Oct 2007 15:39:54 +0200, kath <nitte.sud...@gmail.com> wrote:
> > Hi,
>
> > i have a array of string(say array is of length 1000). I want to
> > compare those string in array with one table column
> > - whether that table column has a string
> > if yes
> > do nothing.
> > if no
> > then insert that string into table.
> > - whether table has obsolete row i.e, the one present in table and
> > not in array
> > then delete that row.
>
> > How do i go about this, because i see, it is not feasible to loop
> > through array and search table to find new string OR loop through each
> > row from table to find some obsolete row
>
> > How can i accomplish this task more feasibly(without running query for
> > each string, for comparission)? Is there any way to find this kind of
> > problem. I would have been easy if i had to compare two tables(with
> > UNION and INTERSECT), but it is not the case.
>
> Aside from the temporary table solution given, this can also be done using
> two queries:
>
> 0: set up table:
> Give the field a UNIQUE index
> 1: delete:
> DELETE FROM tablename WHERE fieldname NOT IN ('list','of','values');
> 2: insert:
> INSERT IGNORE INTO tablename (fieldname) VALUES ('list'),('of'),('values');
> (allthough INSERT ... ON DUPLICATE KEY UPDATE ... could be more suited to
> your needs)
>
> From the manual:
> If you use the IGNORE keyword, errors that occur while executing the
> INSERT statement are treated as warnings instead. For example, without
> IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY
> value in the table causes a duplicate-key error and the statement is
> aborted. With IGNORE, the row still is not inserted, but no error is
> issued. Data conversions that would trigger errors abort the statement if
> IGNORE is not specified. With IGNORE, invalid values are adjusted to the
> closest values and inserted; warnings are produced but the statement does
> not abort. You can determine with the mysql_info() C API function how many
> rows were actually inserted into the table.
>
> If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would
> cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the
> old row is performed. See Section 13.2.4.3, 'INSERT ... ON DUPLICATE KEY
> UPDATE Syntax'. ON DUPLICATE KEY UPDATE was added in MySQL 4.1.0.
>
> --
> Rik Wasmus- Hide quoted text -
>
> - Show quoted text -

Depending on teh size of the table, I would expect the NOT IN
('list','of','values') to be a bit slow (with 1000 values). I'd be
interesting to know the relative performance.