This is my first S/O question! I'm stumped on what seems to be a basic query; given a list of values for various rows, return only the values that have changed. I am building a live-updating feature for a ordering system and I need to update rows on the client browser that have changed since the last (5 second) polling interval. Since I'm dealing with thousands of rows I need to only update what's changed.
Here's what my table looks like:
id, order_number, department
My client sends a list of two values (x1000+ items):
'department' is the value that I'm looking to see if a change has occurred.
I need a way to query the DB once with the entire list from the client and have MySQL return a list of only the changed items. Given a list of 1500 orders, it's likely that only one will ever be changed at a time, so I think for efficiency it makes sense to have the DB engine do this for me rather than looping through the list and doing a query each time. I tried doing it with a temp table and while it kind of worked, I found that the DROP TABLE at the end precluded my query results from being returned:
CREATE TEMPORARY TABLE `orderlist` ( `order_number` varchar(128) DEFAULT NULL, `department` int(11) DEFAULT '1' ); INSERT INTO orderlist (order_id, department) VALUES ('180425','2'); INSERT INTO orderlist (order_id, department) VALUES ('180426','2'); INSERT INTO orderlist (order_id, department) VALUES ('180427','3'); SELECT orders.order_number, orders.department FROM orders JOIN orderlist USING(order_number) WHERE orderlist.department != orders.department; DROP TABLE orderlist;