Skip to main content

Trick: Use Microsoft Excel to produce bulk sets of SQL Commands for your database

VMware Fusion vs. Parallels Desktop Excel
VMware Fusion vs. Parallels Desktop Excel (Photo credit: Wikipedia)
Our example is about UPDATE statements but you will see that its easy to form any sql statement instead of our update. Furhermore, in this example, we will use one table from the standard  Joomla database which is  mySQL  and  phpmyadmin  for browsing our database.

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.


Enhanced by Zemanta

Popular posts from this blog

Enable AMD's Radeon Chill feature for all games.

Since 2014 AMD has been following the tradition of releasing new performance and usability features in their December driver, and 2016 was no exception. Among the myriad of new features and enhancements, was also one that passed almost unnoticed, called Radeon Chill.

Stemming from AMD's acquisition of HiAlgo earlier in 2016, it offers the possibility of huge power and thermal savings while gaming, by monitoring screen movement and adjusting the frame rate accordingly. There is even the claim that it could also reduce response times since the GPU is not occupied with rendering as often.

Its only issue: it's not global, and it's based on a white list of applications to function. But fear not, for we are here. Using a trick as old as operating systems themselves, you can try to make it work with every title. Let's see how to do that.
Step One: Activate Radeon Chill. Doing that is fairly easy. Right click on your desktop and select "Radeon Settings":

Then naviga…

Your first post in your Facebook Page, some simple tips

Many new pages are created on Facebook every day by brands that need to use the network for wither exposure or marketing or both. There are many small businesses that want to use Facebook and need to know about some initial tips to get started with their facebook page.

Facebook page management is no rocket science, but it can lead your brand to either a successful presence or nothing at all, if you don't pay attention to some details. You have to start with the basics. Your every-day process with your page is your posts, information you share on your page and your fans will see. First take a note that not all of your fans will see your post. Let's say you have 1000 fans, only about 10-15% of your fans are expected to actually see that post. What's important here is to make your post appealing so whoever sees it, will be able to engage with it, thus either comment, share or like it.

Take a look at our video for those tips :



Every time a Facebook user likes, share or commen…

Resident Evil 7 no stutter guide for 30 and 60 fps systems

The release of the last Resident Evil game has had us go from indifferent, to cautiously optimistic, to infatuated with one of the best releases in the series; a game on par with the couple of initial classics and the fourth instalment. Capcom decided to drop the third person over-the-shoulder perspective for this one and go with the first person camera, refreshing the whole series in the process. Make no mistake, this is one of the greats, and you owe it to yourself to at least try it out even if you aren't into horror games at all. Unlike other publishers, Capcom has been kind enough to provide a demo of the first hour of the game in Steam. Go ahead and try it.

The game itself is quite well optimized, as it is one of the initial PlayStation VR titles released and that means it has to adhere to specific latency requirements. The good news for PC gamers (sorry, no PC VR yet) is a 60fps target where most older GPUs and systems can have a proper experience. Unfortunately, the game …