Let’s say you’re building a system that stores bids (for an ad, for an auction, doesn’t matter) from an external source. Each bid is a value, item/product ID, and timestamp.
The external source of the bid doesn’t push updates. Your program will have to go fetch them. However, the bid source only gives you the latest bids. No linear history, which is what your program will do.
The process is pretty simple: every X minutes your program will go fetch all the bids and insert them into your local bids table if they have a later timestamp than the current stored bid.
for bid in fetch_bids() if (bid.timestamp > fetch_item_bid(bid.item_id).timestamp) insert_bid(bid) endif endfor
How would you do this with just a single SQL query? You’ll have to use an INSERT [...] SELECT
query with a bit of extra magic.
Set Up Your Schema
CREATE TABLE bids ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT, item_id INT UNSIGNED NOT NULL, bid_value DECIMAL(10, 2) NOT NULL, bid_date DATETIME NOT NULL, PRIMARY KEY (ID) ) Engine=InnoDB;
Evolving The Insert
Inserting a bid is very simple:
INSERT INTO bids (item_id, bid_value, bid_date) VALUES (1, 20.00, '2013-10-18 10:30:00');
We’ll keep the INSERT
part of the above query intact as we continue on. To insert only if the timestamp (bid_date
in the table above) is later than the stored timestamp, we’ll need to use a SELECT statement. That said, we don’t really want to select anything: we already have our values to insert from the external source. Instead of using a field list, we’ll use real values in the SELECT
part of our query:
INSERT INTO bids (item_id, bid_value, bid_date) SELECT 1, 20.00, '2013-10-18 10:30:00';
With the SELECT in place, we can add FROM
, WHERE
, and HAVING
clauses:
INSERT INTO bids (item_id, bid_value, bid_date) SELECT 1, 20.00, '2013-10-18 10:30:00' FROM bids WHERE item_id = 1 HAVING MAX(bid_date) < '2013-10-18 10:30:00';
The above is a bit strange but makes more sense if you look only at the SELECT
part. If the WHERE
and HAVING
conditions are not met the result set will be empty. If that happens, nothing gets selected — not even the static values — and nothing is inserted. Since we want to check the bids
table, that’s where we’ll select from.
There are limitations to this method, of course. The biggest one being that if item_id
wasn’t already in the table the WHERE
clause will fail and nothing will be inserted. To get around that your program might need to select all item ID’s present and insert if a bid is encountered without an existing item ID:
existing_ids = get_existing_item_ids() for bid in fetch_bids() if bid.item_id not in existing_ids insert_bid(bid) continue endif maybe_insert_bid(bid) // the SQL from above endfor
Working With Bound Parameters
The above will even work with bound parameters. Here’s quick example in PHP using Doctrine DBAL.
<?php // require whatever files you need... require __DIR__ . '/vendor/autoload.php'; $config = new DoctrineDBALConfiguration(); $params = array( 'dbname' => 'is_tut', 'user' => 'root', 'password' => null, 'host' => 'localhost', 'driver' => 'pdo_mysql', ); $conn = DoctrineDBALDriverManager::getConnection($params, $config); $res = $conn->executeUpdate( 'INSERT INTO bids (item_id, bid_value, bid_date)' . ' SELECT :item_id, :bid_value, :bid_date' . ' FROM bids' . ' WHERE item_id = :item_id' . ' HAVING MAX(bid_date) < :bid_date', array( 'item_id' => 1, 'bid_value' => 20.05, 'bid_date' => '2013-10-18 10:45:00' ) ); echo 'Inserted ', $res, ' rows!', PHP_EOL;