I had one row in orders_test. I issued an update to change it's order number. It was 3:00am and I was sleepy, so I accidentally left of the "_test" in the table name. As a result, my company's order table now consists of a single order with about 100,000 line items.
My hosting provider informed me at 3:05am that their free backup service (which I thought had been doing daily backups for the past several years) doesn't include our server type.
Double Oops. (This oops turned me pale.)
By 3:10am we were subscribed to their daily backup service, but that was ten minutes too late.
So now I have to put together what I've got. I've got the data, but after eight solid hours I'm still struggling and still exhausted.
The orders table has these relevant fields: detailnum, orderid, and orddate. Detailnum is a unique integer identifier for every line item. The data in this field is fully intact. Orderid is the order number - the field I need to repopulate. A single order number appears on as many rows as there are line items on the order. Orderid is a varchar. Orddate is a datetime including seconds. Thankfully, it's rare for two orders to be submitted in the same second. I've identified the orders where that happened and adjusted the datetimes by a second so that every orderid has its own datetime. This is going to be key to repopulating orderid. (Thank god the data wasn't as normalized as it could be, or those datetimes wouldn't be repeated and I'd be even more up the river.)
I also have a list of all the actual order numbers, divorced from the rest of the table. Each appears once in the list, even though it should appear multiple times in the table.
So, given two tables (simplifying the entry in orddate for legibility):
ORDERS: IDLIST: detailnum orddate orderid orderid --------- ------- ------- ------- 1 TS-1 "Argh!" "A6" 2 TS-2 "Argh!" "A7" 3 TS-2 "Argh!" and... "A8" 4 TS-2 "Argh!" "A9" 5 TS-3 "Argh!" . 6 TS-4 "Argh!" . 7 TS-4 "Argh!" . . . . . . . . . .
How might you go about updating ORDERS to look like this:
ORDERS: detailnum orddate orderid --------- ------- ------- 1 TS-1 "A6" 2 TS-2 "A7" 3 TS-2 "A7" 4 TS-2 "A7" 5 TS-3 "A8" 6 TS-4 "A9" 7 TS-4 "A9" . . . . . . . . .
The list of orderids is complete and in order, and the list of detailnums is complete and in order, and the number of repeated timestamps tells how many times to repeat each orderid.
But short of entering 100K orderids by hand, I can't figure a way to do it.
Much of my work thus far has been focused on getting that orderid list compiled and verified, and looking for other sources that might match the data up. There have been many failures in that department. Finally getting it to this stage tells me that a solution is definitely possible in theory, but I don't have a clue how to make it happen.
Any help would be greatly appreciated!