MySQL Query's

View previous topic View next topic Go down

20100115

Post 

MySQL Query's




Here I will write about some MySQL query's I have made, some of them might be useful, some might be learnful and other might be complete waste of display space, but just felt like sharing them. I will also be updating it often so check back often for more "nice" new query's.
Change a number in a field with a calculation
For a gameserver (Lineage II) that I run at home I needed to update a field to make it a thousand times larger, some of them had values that where to low. To do this I had to exclude all the 0 values and everything above 11443841970 (Unix Time). The result was the following script:
Code:
UPDATE table SET field = field * 1000 WHERE field != '0' AND field <= '11443841970';

Fill one field by calculating 2 others
Again, for my gameserver. In the previous script I changed the fields that I already added manually, but now I wanted to script the other empty fields (I'm an ITer = Lazy :p). In this gameserver they check the last time your character was online and how long in total he/she has been online. I figured I could use these values to decide when a character was created. Ofcourse this only should go for characters that had a 0 value for creation date. This ended up in the following script:
Code:
UPDATE table SET createTime = lastAccess - onlinetime * 10000 WHERE createTime = '0';
Why the * 10000? Well, that was because I figured no one was online 24/7 after they made a character...it's just a random value but shows you can use multiple calculations in a query.
avatar
thierry

Posts : 2238
Join date : 2009-08-18
Age : 35
Location : Zoetermeer

View user profile

Back to top Go down

- Similar topics
Share this post on: Excite BookmarksDiggRedditDel.icio.usGoogleLiveSlashdotNetscapeTechnoratiStumbleUponNewsvineFurlYahooSmarking

 
Permissions in this forum:
You cannot reply to topics in this forum