0

How can I create an Upsert functionality for a table with a composite key when sending the query via php?

I have an order_items table whose key is both an order number and an item number. I then have an api to a store that returns an xml document of order elements (that I then parse into arrays). I want to take this string and update the ones that already exist and insert the ones that don't. Since there could be hundreds of orders (and possibly thousands of line items), I don't want to run a query to check if the item exists each for each line item.

I thought Merge might be the answer, but my data doesn't exist in a table (it is an array that I am going to write to an a table). Is there a way to merge a string of data with an existing table? For example, something equivalent to:

$query = "IF a.order_id and a.item_id --already exist
update db.dbo.order_items
SET a.quantity = $qty, a.status = $status ....
ELSE
INSERT (order_id, item_id, quantity, status)
VALUES 
($order_id, $item_id, $qty, $status)";

run_query($query);

Thanks for any help.

Mike
  • 1,718
  • 3
  • 30
  • 58
  • **warning** your code maybe vulnerable to sql injection attacks! – Daniel A. White Nov 30 '12 at 01:19
  • This isn't actual code, it is just an example of the functionality I am looking for. – Mike Nov 30 '12 at 01:22
  • 1
    Check out - [stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server](http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) – Sean Nov 30 '12 at 01:31

1 Answers1

1

Here was the solution that met all my requirements if anyone else comes across a problem like this:

merge <table> WITH (HOLDLOCK) as target
USING (VALUES('00001111','385480486071',10, '10.00','3.00','0.00'))
AS source (order_id, item_code, item_quantity, item_price, shipping_price, discount)
ON target.order_id = source.order_id AND target.item_code = source.item_code
WHEN MATCHED THEN
update
SET
item_quantity = source.item_quantity, target.item_price = source.item_price, target.shipping_price = source.shipping_price, discount = source.discount
WHEN NOT MATCHED THEN
INSERT (order_id, item_code, item_quantity, item_price, shipping_price, discount)
values(source.order_id, source.item_code, source.item_quantity, source.item_price, source.shipping_price, source.discount);
Mike
  • 1,718
  • 3
  • 30
  • 58