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.


thanks,
kath.

On 26 Oct, 14:39, 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.
>
> thanks,
> kath.

Load the array into a temporary table and use a MySQL intersect
construct (http://www.bitbybit.dk/carsten/blog/?p=71)

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

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.

On Fri, 26 Oct 2007 16:27:48 +0200, Captain Paralytic
<paul_lautman@yahoo.com> wrote:
> 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:
>> > 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 can i accomplish this task more feasibly(without running query for
>> > each string, for comparission)?
>>
>> 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)
>>
>
> 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.

It would certainly depend on that. The kind of solution I offered is
easily usable with for instance forms on the web, where one would some
properties of an object as checkboxes, which the user could check &
uncheck (thus resulting in formentioned array and requirements). That
would for UI/clarities sake have to be limited to no more then 20 or 30
choices. In that case this would be perfectly suitable, especially with
the UNIQUE key on the column.

When the data is indeed over 100's of rows or so I'd definitely look into
the temporary table solution.
--
Rik Wasmus

>> Load the array into a temporary table and use a MySQL intersect
I would not consider this is better idea because, there creating table
inserting values will take lot of SQL queries(statements).
- CREATE statement = 1
- INSERT statement = depending on the size of array
- DELETE statement = 1

>> 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)

Looks good. But i have doubt again, i am using JAVA to do this task. I
don't know exactly whether i can use String[] (array of String in
JAVA) in the DELETE query you have mentioned. I can construct a String
out list of String so that i can query, is it right way?.

If i get how to form a query for list of values, for above DELETE then
I as well get idea to INSERT query.

How do i form a query for list of String in JAVA?.

Thanks for your input,
best regards,
kath.

On Fri, 26 Oct 2007 16:48:34 +0200, kath <nitte.sudhir@gmail.com> wrote:

>>> Load the array into a temporary table and use a MySQL intersect
> I would not consider this is better idea because, there creating table
> inserting values will take lot of SQL queries(statements).
> - CREATE statement = 1
> - INSERT statement = depending on the size of array

No, 1 INSERT statement would do it.

> - DELETE statement = 1

A lot of SQL queries (actually this is quite modest amount) doesn't
necessarily mean it takes longer.

>>> 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)
>
> Looks good. But i have doubt again, i am using JAVA to do this task. I
> don't know exactly whether i can use String[] (array of String in
> JAVA) in the DELETE query you have mentioned. I can construct a String
> out list of String so that i can query, is it right way?.
>
> If i get how to form a query for list of values, for above DELETE then
> I as well get idea to INSERT query.
>
> How do i form a query for list of String in JAVA?.

Personally I stay as far away from JAVA as I can, put I assume an array in
JAVA can be imploded/joined somehow to a string, which would make creating
the query string quite easy.
--
Rik Wasmus