Tricky Sql Statements
Here are some tricky Sql statements that can be used in some specific cases. I figured them out in certain cases where I needed to do data manipulation directly on the database instead of writing script to accomplish them. I will describe them in case study style.
Case 1:
In one of my project there is a user table containing fields like userId, UserName, UserLoginID, Password etc. The UserLoginID field is basically the email address. Almost 95% of the email addresses belongs to a specific domain that is the domain of the project. Let’s say testproject.com. The table contains approx 12,000 records. In that project client decided to shift their main tld from .com to .org like testproject.com to testproject.org. Now they asked me to change the all the users’ email from .com to .org. That means user1@testproject.com to user1@testproject.org I needed to do this job within a shortest span of time. So I decided to run a sql command directly in the database to do the modification. A simple UPDATE sql won’t work in that situation as I needed to change some part of a field and also update statement shouldn’t affect those rows which do not contain testproject.com. So I tried with different types of update sql to do the job but finally derived the following one :-
[code lang=”sql”]
UPDATE tblUser SET UserLoginID = REPLACE(UserLoginID, ‘ testproject.org’, ‘ testproject.com’);
[/code]
The beauty of this statement lies in the usage of replace function. This replace function will synchronously select records having ‘testproject.com’ implementing functionality of the Where clause and generate the value to be set for the field UserLoginID as
Case 2:
This case was relatively easy. I need to find out unique entries from a table. It was not a hard job for me but still I googled a bit for this purpose. Now after some study I come to a position where I could do that using two different sql statements. They are :-
[code lang=”sql”]
SELECT product_code FROM tblproductdetails GROUP BY product_code;
[/code]
and
[code lang=”sql”]
SELECT DISTINCT product_code FROM tblproductdetails;
[/code]
While looking at the results I found both of them produces same result but the only differnce is Select DISTINCT does not sort the result set like GROUP BY does. So I wasn’t sure which one should used. SELECT DISTINCT vs GROUP BY – udbug | Google Groups helped me to chose the correct statement as Select DISTINCT. So I used the following :-
[code lang=”sql”]
SELECT DISTINCT product_code FROM tblproductdetails ORDER BY product_code
[/code]
Case 3:
This is also related with the earlier case of unique entries. This time I need to find out the ids which have duplicate entries. Almost the opposite job than the previous one. Well this statement also uses distinct but along with Group by clause gives the entries which have duplicates in the table. Here is the statement.
[code lang=”sql”]
SELECT DISTINCT regn_id from tblstudentdetails group by regn_id HAVING count(regn_id) > 1;
[/code]
This statement is very useful to find out entries which have duplicates in the tables.
Somebody asked me earlier how do I learn php and database? My answer was – learn the basic, find some real-life project to work for, take the challenge to complete it. You would face lots and lots problem while doing that. Don’t try to get rid of them but try to solve them. When you would finish the job you will understand the difference in you in terms of practical knowledge. I hope you all agree with me. 🙂
