0

I am using swing menu for my Java app. I also have MySQL database. There's a column (named brind) in one of the tables in the database which has to have unique values (other than ID column where values are auto inserted). So through text fields on my JFrame form I will try to insert some values in that table but only if that brind value does not already exist in the table (that is, in the column). I need to have ONLY ONE mysql query for this, call it conditional insert. How do I do this? Thanks

Jason C
  • 34,234
  • 12
  • 103
  • 151
user2370759
  • 37
  • 1
  • 2
  • 6
  • The query will through an exception if you try to add a non-unique value in a unique column. Just handle that exception. Or, start a transaction, check if the value exists and if it does not, insert it and commit. Otherwise, rollback. – Sotirios Delimanolis Aug 08 '13 at 17:56

3 Answers3

1

A query like the following should do what you are asking:

INSERT INTO `thetable`
    SELECT 'values', 'to', 'insert' 
    WHERE NOT EXISTS (
        SELECT * 
        FROM `thetable`
        WHERE brind='whatever'
    )

Where values,to,insert should be replaced with the values of the fields you are inserting.

However, you would be better served by some of the other suggestions here, e.g.

  1. Check for the value first and don't insert if it exists (do this within one transaction to handle concurrency issues, presuming your transaction isolation is set appropriately),
  2. Attempt the insert and handle the unique constraint violation failure.

Option 2 is a good option, in my opinion.

Jason C
  • 34,234
  • 12
  • 103
  • 151
0
REPLACE INTO myTable
SET brind = 'someValue'

I think you have a database design problem. If the column brind, has to be unique, the it should probably serve as the primary key.

Pete B.
  • 3,020
  • 6
  • 22
  • 36
  • I don't believe this does what the OP described as it will replace a value that does exist, rather than fail if the value already exists. Agreed on the design problem though. – Jason C Aug 08 '13 at 18:05
  • I've also thought of making brind primary key, but I am given a ready made database so I am not allowed to change that. – user2370759 Aug 08 '13 at 20:22
0

will try to insert some values in that table but only if that brind value does not already exist in the table (that is, in the column)

That can be done in database level and application level.

As database level is simplest , safest. Make an index on that column and make it unique (and not identifier!) and the database will not insert if is already exist that value. Here is how to do it.

The application level: you will cache what has the database and you will lock the table, disallowing other apps to modify it. Insert it if you want and unlock table.

I would suggest the first one solution

Community
  • 1
  • 1