1

I'm using java and I want to insert to mysql database a data if not exist, Also I want to update that data if exist. but I couldn't find mysql command for this.

I found this code for Insert but this is not what I want

INSERT INTO contacts
(contact_id, contact_name)
SELECT supplier_id, supplier_name
FROM suppliers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE suppliers.supplier_id = orders.supplier_id);

For update, I found this code. but this is not what I want.

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT *
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

What I want to do is something like this

UPDATE student SET student_score = 20 where student_id = 1 WHERE EXIST ( select * from student where student_id = 1;
Guns
  • 2,678
  • 2
  • 23
  • 51
user3536626
  • 15
  • 6
  • 1
    why you need additional check with subquery then this command is more than enough to do the update `UPDATE student SET student_score = 20 where student_id = 1` – Abhik Chakraborty May 01 '14 at 18:12
  • possible duplicate of [Insert to table or update if exists (MySQL)](http://stackoverflow.com/questions/4205181/insert-to-table-or-update-if-exists-mysql) – hoaz May 01 '14 at 18:13
  • It sounds like you want `INSERT ... ON DUPLICATE KEY UPDATE`. [Tutorial here](http://mechanics.flite.com/blog/2013/09/30/how-to-do-an-upsert-in-mysql/) – cpilko May 01 '14 at 18:15

1 Answers1

0

You can use: insert into table_name (id, name, firstname) values(1, "Sessi", "Brahim") on duplicate key update name=values(name), firstname=values(firstname)

Adapt it to your query.

javadev
  • 1,639
  • 2
  • 17
  • 35
  • but this just update value, it doesnt insert a new one if not exist. – user3536626 May 01 '14 at 19:02
  • It does. The query I wrote inserts in table_name if the id does not exist. If it exists a duplicate flag will be raised and the update query will be executed, instead. – javadev May 02 '14 at 12:04