mysql only insert row if value does not exists

Question

A complex mysql question! I only want to insert the last value (with the zero values) if there is no other row with value 1420070400, but i cant put an index on the row (so i can use on duplicate key). Is there a way to do this on an other way?

INSERT INTO data_prijzen_advertentie (
    `ID_advertentie`,`jaar`,`rijnr`,`status_prijs`,`datum_dag`,`timestamp_dag`,
    `prijs_maand`,`prijs_week`,`prijs_midweek`,`prijs_langweekend`,`prijs_weekend`,
    `prijs_dag`,`prijs_ochtend`,`prijs_middag`
)
VALUES
    (100,2014,1,1,'12-05-2014',1399852800,0,100,0,75,0,0,0,0),
    (100,2014,2,1,'23-05-2014',1400803200,0,75,0,101,0,0,0,0),
    (100,2014,3,1,'30-05-2014',1401408000,0,100,0,75,0,0,0,0),
    (100,2014,4,1,'01-01-2015',1420070400,0,0,0,0,0,0,0,0)
ON DUPLICATE KEY UPDATE
    status_prijs = VALUES(status_prijs), datum_dag = VALUES(datum_dag),
    timestamp_dag = VALUES(timestamp_dag), prijs_maand = VALUES(prijs_maand),
    prijs_week = VALUES(prijs_week), prijs_midweek = VALUES(prijs_midweek),
    prijs_langweekend = VALUES(prijs_langweekend), prijs_weekend = VALUES(prijs_weekend),
    prijs_dag = VALUES(prijs_dag), prijs_ochtend = VALUES(prijs_ochtend),
    prijs_middag = VALUES(prijs_middag);

why can't you put a unique (not necessarily primary) index on this field? — Ejaz, May 12 '14 at 19:34
Because there is a situation that there are 2 the same unique values... — Jilco Tigchelaar, May 12 '14 at 19:35
Is it a requirement that you insert multiple rows in one statement? — Patrick Q, May 12 '14 at 19:35
Yes. If you can insert one row per query, then this can be solved with something like [this](http://stackoverflow.com/a/19868883/1505169) or [this](http://stackoverflow.com/a/8534693/1505169). There are many other examples you can find with some searching. — Patrick Q, May 12 '14 at 19:38

spencer7593 · Answer 1 · 2014-05-12T20:15:38.650

One way to do this is to use a SELECT in place of the VALUES clause. Use the SELECT statement to return the rows you want inserted. For example:

  SELECT 100 AS a,2014 AS b,1 AS c,1 AS d,'12-05-2014' AS e
         ,1399852800 AS timestamp_dag
         ,0 AS g,100 AS h,0 AS i,75 AS j,0 AS k,0 AS l,0 AS m,0 AS n
  UNION ALL
  SELECT 100,2014,2,1,'23-05-2014',1400803200,0,75,0,101,0,0,0,0
  UNION ALL
  SELECT 100,2014,3,1,'30-05-2014',1401408000,0,100,0,75,0,0,0,0
  UNION ALL
  SELECT 100,2014,4,1,'01-01-2015',1420070400,0,0,0,0,0,0,0,0

With a SELECT, it's also possible to check for rows that already exist in the table. You can use that query above as an inline view (i.e. wrap that query in parens and assign an alias for the derived table) and write a SELECT against it (like it was a table), and use a NOT EXISTS predicate or an anti-join pattern to suppress the return of rows where a "matching" row already exists in the target table. For example:

  SELECT s.*
    FROM ( SELECT 100 AS a,2014 AS b,1 AS c,1 AS d,'12-05-2014' AS e
                  ,1399852800 AS timestamp_dag
                  ,0 AS g,100 AS h,0 AS i,75 AS j,0 AS k,0 AS l,0 AS m,0 AS n
            UNION ALL
           SELECT 100,2014,2,1,'23-05-2014',1400803200,0,75,0,101,0,0,0,0
            UNION ALL
           SELECT 100,2014,3,1,'30-05-2014',1401408000,0,100,0,75,0,0,0,0
            UNION ALL
           SELECT 100,2014,4,1,'01-01-2015',1420070400,0,0,0,0,0,0,0,0
         ) s
    LEFT
    JOIN data_prijzen_advertentie t
      ON t.timestamp_dag = s.timestamp_dag
   WHERE t.timestamp_dag IS NULL

(NOTE: the LEFT JOIN operation returns all rows from the derived table s, along with matching rows from t; the "trick" is to use a WHERE clause that eliminates all rows that found a match, so we are left with rows from s that didn't have a matching row in t. The same thing could be achieved with a NOT EXISTS predicate with a correlated subquery.)

It may be necessary to wrap this query in parens and reference it as an inline view (so it is again, a derived table) to avoid a mutating table issue/error. For example:

  SELECT r.*
    FROM (
           SELECT s.*
            FROM ( 
                   SELECT 100 AS a,2014 AS b,1 AS c,1 AS d,'12-05-2014' AS e
                          ,1399852800 AS timestamp_dag
                          ,0 AS g,100 AS h,0 AS i,75 AS j,0 AS k,0 AS l,0 AS m,0 AS n
                    UNION ALL
                   SELECT 100,2014,2,1,'23-05-2014',1400803200,0,75,0,101,0,0,0,0
                    UNION ALL
                   SELECT 100,2014,3,1,'30-05-2014',1401408000,0,100,0,75,0,0,0,0
                    UNION ALL
                   SELECT 100,2014,4,1,'01-01-2015',1420070400,0,0,0,0,0,0,0,0
                 ) s
            LEFT
            JOIN data_prijzen_advertentie t
              ON t.timestamp_dag = s.timestamp_dag
           WHERE t.timestamp_dag IS NULL
         ) r

Once you have a query working that returns the rows you want (excluding rows where a matching row already exists in the target table), you can substitute the VALUES clause in the INSERT statement with the query.

Note: this only checks for existence of rows already in the table when the query runs. This doesn't check the resultset returned by the query, to see if there are two (or more) rows with the timestamp_dag value.

mysql only insert row if value does not exists

1 Answers1