MySQL insert if select output <> Value

Question

I'm writing a Logging System for Items where i track the Quantity and Type of various Objects. And i need to write a Insert Query where it only imports if the Quantity (qty) has changed since the last time.

This is the Query to get the last inserted Quantity:

SELECT qty FROM `qty` WHERE object='object_name' AND type='type' ORDER BY UNIX_TIMESTAMP(timestamp) DESC LIMIT 1

But now how do i say: Import only if quantity given by Programm is not the Quantity given by the Query above

Edit: Here is the Normal insert:

INSERT INTO `qty` (qty, object, type) VALUES ("quantity", "object_name", "type")

Edit: I got it working now! thanks everybody for the response! you guys are awesome :)

INSERT INTO qty (qty, object, type)
SELECT * FROM (SELECT 'qty-value', 'object-value', 'type-value') AS tmp
WHERE NOT EXISTS (
 SELECT * FROM (SELECT qty FROM `qty` WHERE object = 'object-value' AND type = 'type-value' ORDER BY UNIX_TIMESTAMP( timestamp ) DESC LIMIT 1) as lastQTY WHERE qty = "qty-value"
) LIMIT 1;

you my find this useful http://stackoverflow.com/questions/15647629/conditional-insert-into-mysql-where-not-exists — Leo, Jul 25 '14 at 12:38

score 0 · Answer 1 · answered Jul 25 '14 at 13:04

If you want to insert new values, try matching the new values to the old values. If there is a match, then filter out the rows. I think the key is using insert . . . select rather than insert . . . values.

The following gives the idea:

INSERT INTO qty(qty, object, type) 
    select @quantity, @object_name", @type
    from (select @quantity as quantity, @object_name as object_name, @type as type
         ) as newrow left outer join
         (SELECT qty.*
          FROM qty
          WHERE object = @object_name AND type = @type
          ORDER BY UNIX_TIMESTAMP(timestamp) DESC
          LIMIT 1
         ) oldrow
         on newrow.quantity = oldrow.quantity and
            newrow.object_name = oldrow.object_name and
            newrow.type = oldrow.type
    where oldrow is null;

score 0 · Answer 2 · answered Jul 25 '14 at 13:16

Think this would do it.

This takes your input values, joins that against a sub query to get the latest timestamp for the object and type, and then joins that against the qty table to get the value of the column qty for the latest timestamp and that the qty is the same as the new qty.

The WHERE clause is then checking that the value of the latest qty is NULL (ie, assuming the qty can not legitimatly be NULL there is no record found )

INSERT INTO `qty_test` (qty, object, type) 
SELECT a.qty, a.object, a.type
FROM 
(
    SELECT 1 AS qty, 1 AS object, 1 AS type
) a
LEFT OUTER JOIN 
(
    SELECT object, type, MAX(timestamp) AS max_timestamp
    FROM qty_test
    GROUP BY object, type
) b
ON a.object = b.object
AND a.type = b.type
LEFT OUTER JOIN qty_test c
ON a.object = c.object
AND a.type = c.type
AND a.qty = c.qty
AND b.max_timestamp = c.timestamp 
WHERE c.qty IS NULL

MySQL insert if select output <> Value

2 Answers2