Tag Archives: Codes

How to identify and delete duplicate records from sql table

Posted by toalakan-web on March 16, 2009 1 comments

Well I’m sure any SQL developer out there would have encountered a problem where the duplicate records in a table have to eliminated for some reasons.

I recently encountered a similar situation where I had to remove the duplicate user name from a table with just one simple query . Here is how I did it:

Here is my tbl_username table having the following columns UID( auto increment primary key), uname (varchar(100)), fname (varchar(100)):

UID Uname fname
1 xyz xyz
2 xyz xyz
3 abc abc
4 qwerty qwerty
5 qwerty qwerty

We would have to remove the duplicate user names in order to do that we first build a sub query that picks up the uname and max id of the duplicate records.

Select uname, max(UID) as latestUID from tbl_username group by uname having count(uname) > 1

Result:

Uname latestUID
xyz 2
qwerty 5

By using the max function we can pick the latest entry to be deleted if you like to pick the lowest use the min() function.

Well if you have got this then its pretty much simple to get the build another sub query on top of this to get the unique IDs and then delete it as usual.

delete from tbl_username
where UID in
(
select latestUID from (
Select uname, max(UID) as latestUID from tbl_username group by uname having count(uname) > 1
)
)

Now the table has the following data in it

UID Uname fname
1 xyz xyz
3 abc abc
4 qwerty qwerty

Well this way of deleting would only work if the table uses an integer as a primary key.

This is the cure but prevention of this kind of situation can be resolved by just using unique keys on the Uname field.

Prevention is always better than cure so grab a SQL book if you have done this mistake.

How to get Current DB name and user in MS SQL server?

Posted by toalakan-web on February 17, 2009 1 comments

Most of the time I forget the command to get the DB name and current user when working. So decided to make post about it no better way to find the info fast.

Here are the 2 commands that basically gets the info.

  • Select DB_name() ;
  • Select Current_user;

Here is a post that my friend wrote on How to Insert Values into an Identity Column which I didn’t know to do before.

BTW he is Microsoft guy not CF :(

How to avoid bots from filling up Online forms using Spry?

Posted by toalakan-web on August 24, 2008 1 comments

I’m sure every one some day or other were annoyed while filling out the painful CAPTCHA image in the form field. I have hated it all the time and its a pain for the users to read through the image and retype it again. Here is a simple solution that I found out while working @ESRI . I’m not sure who developed this solution but it has avoided a lot of bots from filling up online forms.

The solution is to load the form when the user clicks an buttons/link basically any event that an real human can do it. The form could be

  • loaded using an AJAX call. Using ur own favorite Javascript library.
  • Just hide the form and then show when the user clicks on the link.

After putting up the Yahoo online status image on my web site I have been getting a lot of spam IMs to my ID and to prevent this from happening I decided to implement this method. Since I’m a big time fan of Spry I did it using that and here is how I did it.

<script src=”/spry/SpryHTMLPanel.js” type=”text/javascript”></script>

<div id=”YahooStatus”>
<a onclick=”hpanel.loadContent(‘/forms/dsp_Yahoo_status.html’,{async : true}); return false;” href=”javascriot:void();”>

<h2>Get my Yahoo online status</h2>

</a>
</div>

<div id=”rpanel”></div>

<script type=”text/javascript”><!–

var hpanel = new Spry.Widget.HTMLPanel(“rpanel”,{evalScripts : true});

// –></script>

  1. Initially I load up the Spryhtmlpanel.js final.

    <script src=”/spry/SpryHTMLPanel.js” type=”text/javascript”></script>

  2. Then set up an anchor region with onclick event that pulls the page called dsp_yahoo_status.html from forms directory in to the rpanel div region.

    <div id=”YahooStatus”>
    <a onclick=”hpanel.loadContent(‘/forms/dsp_Yahoo_status.html’,{async : true}); return false;” href=”javascriot:void();”>

    <h2>Get my Yahoo online status</h2>

    </a>
    </div>

  3. The rpanel div region is the region that gets loaded with the Yahoo status.

    <div id=”rpanel”></div><

  4. Then initialize the spry html panel widget. The evalscripts tells spry to execute any javascript inside the loaded page. This feature would help us to run validation scripts when an form is loaded instead of a normal text.

    <script type=”text/javascript”><!–

    var hpanel = new Spry.Widget.HTMLPanel(“rpanel”,{evalScripts : true});

    // –></script>

This way only an human would be able to view my Yahoo status. The same concept can be used for loading up forms as well and all this in just 10 line of codes.

To find more info on how to use Spry HTML panel check out this page which has better examples.

Switch to our mobile site