Hello there. I have kind of a strange question. I've been using vlookup function in excel for almost a year now and it makes my work way more easy. And now it's the first time when I need to collect the data from MySQL database. I have an excel file with the column SKU and I need to fill the data for every SKU. The thing is that database has almost 800k rows and I don't know how to handle this amount of information. Is there any way to workaround this without doing every SKU one by one?
I'm confused as to what you are asking. Can you provide DDL, sample data and expected outcome?
SAMPLE:
volvo_skus table
| SKU |
| 205888 |
| 205890 |
| 205892 |
| 205894 |
| 205893 |
| 205899 |
truckparts_table (800k rows approximately)
| CAR_BRAND | CAR_MODEL | CAR_BODY | CAR_KW | CAR_CC | OE_NUMBER | CATEGORY |
VOLVO FM 12 Platform 279 12100 205888 Heating
VOLVO FM 12 Platform 281 12100 205890 Heating
VOLVO FM 12 Platform 278 12100 205892 Heating
VOLVO FM 12 Platform 270 12100 205894 Heating
VOLVO FM 12 Platform 275 12100 205893 Heating
VOLVO FM 12 Platform 279 12100 205895 Heating
VOLVO FM 12 Platform 279 12100 205896 Heating
VOLVO FM 12 Platform 279 12100 205897 Heating
VOLVO FM 12 Platform 279 12100 205898 Heating
VOLVO FM 12 Platform 280 12100 205899 Heating
The output I want:
Third table
| SKU | CAR_BRAND | CAR_MODEL | CAR_BODY | CAR_KW | CAR_CC | CATEGORY |
205888 VOLVO FM 12 Platform 279 12100 Heating
205890 VOLVO FM 12 Platform 281 12100 Heating
205892 VOLVO FM 12 Platform 278 12100 Heating
205894 VOLVO FM 12 Platform 270 12100 Heating
205893 VOLVO FM 12 Platform 275 12100 Heating
205899 VOLVO FM 12 Platform 280 12100 Heating
I have two tables. The first one is 'volvo_skus' which contains only one column with SKU's (30k rows) and the second one is named 'truckparts'. The second one has all the data. It has 15 columns with different info for the product like Category, Parts_info, OE_NUMBER and etc and its nearly 800k rows.
The thing I'm trying to do is: if the SKU in the table volvo_skus matches with the OE_NUMBER in the table truckparts, select and apply all the info for that SKU from the table 'truckparts'. The same principle as VLOOKUP in excel.
did you try to join them?
select *
from volvo_skus v
join truckparts t
on v.sku = t.oe_number
It actually worked with small amount of test data I just created from the both tables in the example.
I have few questions:
Is it possible to remove OE_NUMBERS column in the output and leave only SKU column?
How long it can take to calculate a huge amount of data 25gb?
It depends on indexes. That was a simple sql select statement. instead of select *, you should add the column names (select v.sku, t.car_brand, etc...). I'm not sure why you want to run this against 25Gb of data. Sounds like a database export to me.
Basically yes, I need to collect the data and export it to the excel. After that I will import the file to the new store we are creating right now.