Using EXISTS with MySQL (embedded in a JBDC statement)

[alcoholic]

I'm struggling on how to use the EXISTS condition in an SQL statement. I also think some of my other syntax is wrong so any help there would be appreciated too.

 

So far I've tried to use the following code:

[pre]

                for(int x=0; x<terms.length; x++)

                {

                s.executeUpdate("IF EXISTS(SELECT * FROM mesh_terms m" +

                "WHERE term = '" + terms[ x] + "')" +

                "INSERT INTO article_mesh_terms(article_id, mesh_id)" +

                "VALUES (" + pmcid + ", m.mesh_id )" +

                "ELSE INSERT INTO mesh_terms VALUES(" + terms[ x] + " )" +

                "AND INSERT INTO article_mesh_terms(article_id, mesh_id)" +

                "VALUES (" + pmcid + ", mesh_id= (SELECT mesh_id FROM mesh_terms n" +

                                            "WHERE n.term = '" + terms[ x] + "' ) )");

                }

[/pre]

 

I wish to check if a term from the terms array exists in the database, and if it does, then I only need to insert into the article_mesh_terms table.

However, if the attribute value doesn't exist, then I must first insert it into the mesh_terms table. The mesh_terms table should create a unique ID using auto_increment. But then I need to retrieve this ID to insert into the article_mesh_terms table along with the article_id.

 

I hope that makes sense? Basically, I have a table for articles, a table for mesh_terms... and then the third table which links the two by their ids.

 

Thanks for any help. I'm not so good with my SQL, as you can probably see lol.

[aschk]

You're trying to put logical into a single SQL query, which unfortunately won't work.

You need to do the following steps (each one a diffferent SQL query)

1) start a transaction

2) SELECT from the mesh_terms where the term = 'blah'

3) (Java)if rows > 0, (SQL)INSERT into article_mesh_terms table

4) (Java)else (SQL)INSERT INTO some other table

5) use LAST_INSERT_ID() to get the inserted auto increment id

6) do other stuff

7) COMMIT transaction

 

As of MySQL 5.0 you can use stored procedures, and this may well be a good way to go about it, seeing as you can then shield the Java program from database logic.