|VMware Fusion vs. Parallels Desktop Excel (Photo credit: Wikipedia)|
First step is to log into phpmyadmin and find out the records you need to update
You can get the candidate datase via your own sql query . For example you may only need to update the sales of March and not the complete set of sales. In this window you will select all the grid with data and copy it to your clipboard ( control + c ). Then you will open a new notepad document and paste in it ( control + v ) the copied data.
Now you will select all here ( contol + a ) and then copy again ( control + c ). We do this intermediate step to clear our data of any HTML element that may pass on to the Excel sheet and cripple our effort. Next open a new MS Excel document. Paste the copied data in it :
Data here are full of junk coming from phpmyadmin’s UI ;so we need to clear them out by deleting some columns and rows that do now contain actual data. The clearing process depends on how you have copied your data from phpmyadmin of some other database browser. Next the clearing process has produced some straightforward data in our excel sheet :
Once we have cleared our data, we only keep the columns we need, not the complete data set. We then create a new column in our sheet and inside it we form our sql statement, to retreive data from the other columns. Mind the syntax inside MS Excel on how to concat strings and values. Keep in mind that those statements must comply with your database’s sql syntax.
Now if we change the values of the second column ( alias ), the statements will change automatically :
Now we are ready to copy the contents of the “commands” column, paste it to our phpmyadmin and then execute :
This little trick will only work on small datasets though. If your want to massivelly edit your database’s records, then you must use standard SQL commands.