On Fri, 26 Oct 2007 15:39:54 +0200, kath <nitte.sudhir@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