0

I have got a table with name table_listnames whose structure is given below 

mysql> desc table_listnames;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.04 sec)

It has got sample data as shown 

mysql> select * from table_listnames;
+----+------------+
| id | name       |
+----+------------+
|  6 | WWW        |
|  7 | WWWwww     |
|  8 | WWWwwws    |
|  9 | WWWwwwsSSS |
| 10 | asdsda     |
+----+------------+
5 rows in set (0.00 sec)

I have a requirement where if name not found under the table , i need to insert or else do nothing

I am achieving it this way 

String sql = "INSERT INTO table_listnames (name) SELECT name FROM (SELECT ?) AS tmp WHERE NOT EXISTS (SELECT name FROM table_listnames WHERE name = ?) LIMIT 1";


            pst = dbConnection.prepareStatement(sql);
            pst.setString(1, salesName);
            pst.setString(2, salesName);
            pst.executeUpdate();

Is it possible to know the id of the record of the given name in this case

Pawan
  • 31,545
  • 102
  • 256
  • 434
  • Do you want to get the record id if the name is there in the table? or the record id of added row? – smali Jul 10 '15 at 11:50
  • ya for both the cases . (Incase its present or else its inserted newly) – Pawan Jul 10 '15 at 11:52
  • possible duplicate of [How to get the insert ID in JDBC?](http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc) – Mark Rotteveel Jul 10 '15 at 12:15
  • @MarkRotteveel , good that you found it , but i need to get the key in case the record is existing also . – Pawan Jul 10 '15 at 12:19
  • In that case it might be easier to do what ali786 suggested in his (now deleted) answer: add a unique constraint on `name`, just insert (with getting the generated key as suggested in my link) and select if the insert fails because of a duplicate key error. Or do the reverse: select first and insert if nothing is found. – Mark Rotteveel Jul 10 '15 at 12:22

0 Answers0