File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and the fly likes MySQL Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL Query" Watch "MySQL Query" New topic

MySQL Query

Richard Green
Ranch Hand

Joined: Aug 25, 2005
Posts: 536
i have two tables




id text
1something yy1 yy3 blah blah
2 yeah right yy3

I want a sql script to basically go through the names in table1, and see if they are present in table2 and if so, update the "found" field to "Yes"

So, I wrote this

UPDATE table1
SET found = 'Yes'
WHERE exists
( SELECT 1 from table2
where text like concat(concat('%',,'%'))

It worked perfectly in a dev environment (where I had 100 records in table1 and 2), but in prod we have about 2 million records and MySQL just hangs when I run this query.

Any ideas on how to fix this?

Oh btw and table2.text are text fields - I indexed these two fields but that didnt make much difference.

MCSD, SCJP, SCWCD, SCBCD, SCJD (in progress - URLybird 1.2.1)
It is sorta covered in the JavaRanch Style Guide.
subject: MySQL Query
Similar Threads
Any one have idea regarding this query??
Projection from inner join
Hibernate: Different Join in Save() and Get()
Query help
Help with a query?