MySQL Conditional Insert

by

If your DBMS does not impose limitations on which table you select from when you execute an insert, try:

INSERT INTO x_table(instance, user, item) 
    SELECT 919191, 123, 456
        FROM dual
        WHERE NOT EXISTS (SELECT * FROM x_table
                             WHERE user = 123 
                               AND item = 456)

In this, dual is a table with one row only (found originally in Oracle, now in mysql too). The logic is that the SELECT statement generates a single row of data with the required values, but only when the values are not already found.

Alternatively, look at the MERGE statement.

More Related Contents:

  1. “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”
  2. How to set initial value and auto increment in MySQL?
  3. Get the new record primary key ID from MySQL insert query?
  4. Which is faster: multiple single INSERTs or one multiple-row INSERT?
  5. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  6. MySql Table Insert if not exist otherwise update
  7. Increment a database field by 1
  8. Prevent auto increment on MySQL duplicate insert
  9. How do I use properly CASE..WHEN in MySQL
  10. MySQL trigger On Insert/Update events
  11. How do I get the ID of multiple inserted rows in MySQL?
  12. MySQL trigger to update a field to the value of id
  13. Importing a CSV to MySQL with different date format
  14. How to insert data to MySQL with auto-incremented column(field)?
  15. Field ‘id’ doesn’t have a default value?
  16. MySQL INSERT INTO … VALUES and SELECT
  17. MySQL error 1241: Operand should contain 1 column(s)
  18. insert data from one table to another in mysql
  19. MySQL Insert & Joins
  20. How to add a where clause in a MySQL Insert statement?
  21. Reserved word in column name – insert into MySQL [duplicate]
  22. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  23. MySQL LAST_INSERT_ID() used with multiple records INSERT statement
  24. Mysql insert into 2 tables
  25. MySQL – insert data from another table merged with constants
  26. Insert data into table with result from another select query
  27. MySQL: Insert datetime into other datetime field
  28. “Unknown column in ‘field list'”, but column does exist
  29. Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?
  30. Insert and set value with max()+1 problems

Leave a Comment