12 Replies - 1698 Views - Last Post: 31 August 2013 - 03:17 PM

#1 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 10:46 AM

I seriously blew it.

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.

Oops.

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!

Thanks,
Wayne

Is This A Good Question/Topic? 0
  • +

Replies To: Assigning a list of values to rows in a one-to-many way

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3193
  • View blog
  • Posts: 10,697
  • Joined: 12-December 12

Re: Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 11:02 AM

You might should wait for one of the experts here, but my first step would be to add an auto-increment field to the table orderid. Then open and close this table so that the numbers are populated. Then modify this new field to just a number (integer) field. I don't know if MySQL allows an increment field to be modified (without losing the numbers) - it should do, I believe. Even if not, a third integer column could be added and the increment numbers copied across.

The reason I would do this first would be to ensure that the order of the items is not lost during any further manipulation!

I'll pause my advise here though, as my temptation would be to copy the two tables into Excel. This shouldn't be necessary though, as the required editing can be done in SQL.

This post has been edited by andrewsw: 31 August 2013 - 11:22 AM

Was This Post Helpful? 0
  • +
  • -

#3 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Re: Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 11:32 AM

Thanks for the quick response!

Actually, detailnum is already an autoincrement field so I'm covered there.

And copying into Excel and generating a hundred thousand update statements there would be a perfectly acceptable to solution to me. Most of the work I've done so far has, in fact, been doing exactly that. I found a very old backup from when the table only had 25000 records and fixed their orderids that way.

The problem is that I don't know of a way to make Excel populate the cells in this way. If you know of a way to make that happen, I'm all over it!

Wayne
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3193
  • View blog
  • Posts: 10,697
  • Joined: 12-December 12

Re: Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 11:47 AM

It appears you don't need the second table at all. In the screenshot A6 in the first row is typed manually.

Posted Image

The 255 is just a large number to persuade MID() to retrieve all the numbers after the letter "A".

This post has been edited by andrewsw: 31 August 2013 - 11:50 AM

Was This Post Helpful? 0
  • +
  • -

#5 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Re: Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 12:38 PM

If I'm understanding that correctly, it's assuming that no numbers are skipped in the list of orderids. There's no guarantee of "A28234" for example, even if there's an A28233 and an A28235. Also, "A" is just a sample prefix. Contrary to good data normalization, the system puts 1-3 letter prefixes before order numbers to indicate a few things. So it needs to pull from the list.

Thanks for the suggestion, I do appreciate it. Unfortunately I oversimplified in the name of brevity so that my question wouldn't compete with War & Peace.

W
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3193
  • View blog
  • Posts: 10,697
  • Joined: 12-December 12

Re: Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 01:03 PM

Erm, given the apparent urgency or importance of this matter, do you not think that it would be.. sensible to post sample data that accurately represents what you have in your tables? I appreciate that you may not want to post exact code-numbers (although they would be meaningless to anyone here :dontgetit: ) but the sample data needs to accurately reflect your question.

Hint: You can disguise code-numbers by just changing letters and numbers, as long as they don't disguise a pattern that exists in the original data, or introduce a pattern that doesn't exist :whistling:.

This post has been edited by andrewsw: 31 August 2013 - 01:06 PM

Was This Post Helpful? 0
  • +
  • -

#7 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Re: Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 01:31 PM

I'm sorry about that. I described the problem accurately and concisely, but then went overboard on the conciseness of the sample data in an effort to make it easy to understand the problem in hopes of increasing the chances people would look at it. It didn't occur to me that the simple appearance of the sample data would obscure the complication of the description of the problem. The challenge is, as described, to pull the data from a list and populate it into a table, duplicating each list entry a number of times equal to how many times the timestamp repeats at that point in the table, and to do it all in the order of the list.

Here's is some actual live data to be more accurate:
ORDERS:                                                           ORDERIDLIST               ORDERS:
+-----------+---------------------+---------+                     +---------+               +-----------+---------------------+---------+
| detailnum | orddate             | orderid |                     | orderid |               | detailnum | orddate             | orderid |
+-----------+---------------------+---------+                     +---------+               +-----------+---------------------+---------+
|     20484 | 2010-08-25 04:46:11 | 30000   |                     | DF23919 |               |     20484 | 2010-08-25 04:46:11 | DF23919 |
|     20485 | 2010-08-25 04:49:31 | 30000   |                     | C23920  |               |     20485 | 2010-08-25 04:49:31 | C23920  |
|     20486 | 2010-08-25 04:49:31 | 30000   | Combined with this: | TD23922 | To make this: |     20486 | 2010-08-25 04:49:31 | C23920  |
|     20487 | 2010-08-25 04:54:08 | 30000   |                     | D23923  |               |     20487 | 2010-08-25 04:54:08 | TD23922 |
|     20488 | 2010-08-25 04:54:08 | 30000   |                     | SR23924 |               |     20488 | 2010-08-25 04:54:08 | TD23922 |
|     20489 | 2010-08-25 04:54:08 | 30000   |                     +---------+               |     20489 | 2010-08-25 04:54:08 | TD23922 |
|     20490 | 2010-08-25 04:57:35 | 30000   |                                               |     20490 | 2010-08-25 04:57:35 | D23923  |
|     20491 | 2010-08-25 04:57:35 | 30000   |                                               |     20491 | 2010-08-25 04:57:35 | D23923  |
|     20492 | 2010-08-25 04:57:35 | 30000   |                                               |     20492 | 2010-08-25 04:57:35 | D23923  |
|     20493 | 2010-08-25 04:57:35 | 30000   |                                               |     20493 | 2010-08-25 04:57:35 | D23923  |
|     20494 | 2010-08-25 04:57:35 | 30000   |                                               |     20494 | 2010-08-25 04:57:35 | D23923  |
|     20495 | 2010-08-25 04:57:35 | 30000   |                                               |     20495 | 2010-08-25 04:57:35 | D23923  |
|     20496 | 2010-08-25 04:57:35 | 30000   |                                               |     20496 | 2010-08-25 04:57:35 | D23923  |
|     20497 | 2010-08-25 05:06:28 | 30000   |                                               |     20497 | 2010-08-25 05:06:28 | SR23924 |
|     20498 | 2010-08-25 05:06:28 | 30000   |                                               |     20498 | 2010-08-25 05:06:28 | SR23924 |
|     20499 | 2010-08-25 05:06:28 | 30000   |                                               |     20499 | 2010-08-25 05:06:28 | SR23924 |
|     20500 | 2010-08-25 05:06:28 | 30000   |                                               |     20500 | 2010-08-25 05:06:28 | SR23924 |
|     20501 | 2010-08-25 05:06:28 | 30000   |                                               |     20501 | 2010-08-25 05:06:28 | SR23924 |
|     20502 | 2010-08-25 05:06:28 | 30000   |                                               |     20502 | 2010-08-25 05:06:28 | SR23924 |
|     20503 | 2010-08-25 05:06:28 | 30000   |                                               |     20503 | 2010-08-25 05:06:28 | SR23924 |
+-----------+---------------------+---------+                                               +-----------+---------------------+---------+



That orderidlist in the middle is currently sitting in an Excel spreadsheet, but could be easily imported into a table with an autoincremented id column to keep it in order. The orders table is, obviously, in the database, but I've also copied these specific columns, in order, into another Excel spreadsheet for ease of manipulation.

Hopefully that clears up any confusion I caused in my overzealous attempt to avoid confusion. :)/>/>

Thanks,
Wayne

This post has been edited by wzeller: 31 August 2013 - 01:33 PM

Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3193
  • View blog
  • Posts: 10,697
  • Joined: 12-December 12

Re: Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 01:45 PM

Posted Image
Was This Post Helpful? 1
  • +
  • -

#9 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3193
  • View blog
  • Posts: 10,697
  • Joined: 12-December 12

Re: Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 01:56 PM

Posted Image

Posted Image

=IF(B3=B2,C2,C2+1)
=INDEX($F$2:$F$6,C2)

This post has been edited by andrewsw: 31 August 2013 - 01:59 PM

Was This Post Helpful? 1
  • +
  • -

#10 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Re: Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 02:01 PM

That looks... Extremely doable. I've not encountered index() before.

I'll get to work with this solution immediately and let you know how it goes. Thank you! I think my stress may be about to end. :)

Marshall
Was This Post Helpful? 0
  • +
  • -

#11 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3193
  • View blog
  • Posts: 10,697
  • Joined: 12-December 12

Re: Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 02:38 PM

It can be achieved in one formula:

=IF(C3=C2,E2,INDEX($F$2:$F$6,MATCH(E2,$F$2:$F$6,0)+1))

but it is easier to see what is happening, and to understand, if you split it in two.
Was This Post Helpful? 1
  • +
  • -

#12 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Re: Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 03:11 PM

To put it mildly, YOU ROCK.

Posted Image

It worked perfectly, and almost exactly 12 hours after my goof I'm back in business.

Today was a scary day. Thanks for helping me get it fixed!

Wayne
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3193
  • View blog
  • Posts: 10,697
  • Joined: 12-December 12

Re: Assigning a list of values to rows in a one-to-many way

Posted 31 August 2013 - 03:17 PM

Give the cookie to Goofy B)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1