Category Archives: MySQL

mysql-logo

Updating two columns with a subquery in MySQL

mysql-logo

Sometimes you wanted to update columns of one MySQL table by quering another MySQL table and getting a column value from the later table. For example you have a ‘students’ table and you wanted to create a new field ‘totalMarks’ in it and wanted to fill or update by taking sum of the ‘marks’ field from another table named ‘marks’. Here comes the use of subquery to update column. Continue reading

How to delete duplicate MySQL records with duplicate field value (as ID)

For some reason i had duplicate entries in my WP database. The ID field in wp_users table was tempered with and was changed to Non Auto-Increment to insert records on the will. I deleted all ID=null records manually and then tried to change it to AUTO INCREMENT but it came up with Duplicate Entry error and then i found that it had duplicated/multiple entries with same IDs as well. Here’s the remedy i had to apply in order to delete those duplicate entries and change ID field to AUTO INCREMENT once again. Continue reading

A few notes on joining a MySQL table to itself to get unique combination of field data

Just a few notes on joining a MySQL table to itself to get unique combination of data stored in its fields.

Suppose we have a MySQL table named `keywords` having fields `word1`, `word2` and `word3`. Once we have got a few records inserted into this table we would want to show all possible combination of keywords stored in those fields. For example, a select query returns a following set of data: Continue reading

Calculating difference between two mysql dates in seconds

Here is a quick way to calculate the difference between two mysql dates in number of seconds. In my case, i required to fetch all entries for which the difference between current time stamp and date in a table field would be less than 5 minutes. Continue reading