Website Designers and Webmasters

Dedicated to all the tasks Webmasters, Website Developers and Website Designers find themselves facing.

By: Ashton Sanders

SQL – Delete Similar or Identical Records

Oct 20 2009

Filed under: SQL,Webmaster

Deleting similar or identical records in SQL is a lifesaver. I found Brian Cryer’s “SQL How To…” about deleting similar or identical records in SQL. It’s got a lot of information, including the code for MS-Access SQL.

Here’s a quick explanation of what the following code does:

It searches MyTable for and deletes all rows where the dupField is the same except for the row with the lowest uniqueField.

Delete Similar Records

To delete similar records, i.e. where the records are not the same but one field is the same and only one copy needs to be preserved, try the following SQL:

DELETE T1
FROMÂ MyTable T1, MyTable T2
WHEREÂ T1.dupField = T2.dupField
AND T1.uniqueField > T2.uniqueField

To break this down even further, this code would delete all rows in MyTable that had the same value in dupField.

DELETET1
FROM MyTable T1, MyTable T2
WHERE T1.dupField = T2.dupField

This last line restricts the deleting to only rows that are also greater than another row with the same dupField

AND T1.uniqueField > T2.uniqueField

Obviously, you would replace the field and table names to work for your table.

Talk soon,
Ashton Sanders

By: Ashton Sanders

Useful MSSQL Queries and Statements

May 2 2009

Filed under: SQL,Webmaster

Although I primarily use MySQL on linux servers, I’ve had a couple clients using MSSQL databases for all sorts of things from email marketing to order tracking. For these clients, I use Microsoft SQL Sever Management Studio Express and have saved many of the MSSQL queries I’ve had to create. Some of these will work fine in MySQL as is or with minimal tweaking.

Here’s a collection of the useful MSSQL Queries I’ve used and saved. This is by no means a comprehensive list of all the MSQL Queries and MSSQL statements, but I hope they can be useful for you.

MSSQL Variables

Being able to declare MSSQL Variables has saved so much time for sets queries that I need to run multiple times with slight variations.

This is a very basic example, but has everything you need. MSSQL Variables must begin with a “at symbol” (@). You first declare the MSSQL variables and assign it a character type (in this case, I’m just using varchar(55). 55 is the maximum number of characters to be stored in the variable. Then you set a value for the variables.

Declare @email1 varchar(55), @name1 varchar(55)
set @email1 = ‘%websitesinaflash.com%’
set @name1 = ‘%Ashton Sanders%’

SELECT *
FROMÂ Â email_database
WHERE email_address LIKE @email1
AND first_name LIKE @name1

MSSQL UNION

Continue Reading…

By: Ashton Sanders

mssql_connect(): unable to connect to server:

Aug 31 2008

Filed under: PHP,SQL

I have a PHP script that I run locally (on my Windows XP Machine) that accesses a MSSQL server using the mssql_connect() function. I used xampp to get PHP on my machine.

Today I finished getting this to work on my second computer. The first time I did this, I also ran into a million problem – but didn’t note any of them. So here I am rectifying my previous mistakes in the hope of not having to search for the same solution next time.

The error I was getting was:

mssql_connect(): unable to connect to server: ‘www.ashtonsanders.com’

(Domain changed for security purposes.)

I handled it by replacing ntwdblib.dll with this ntwdblib.dll.zip in these two folders:

xampp/php
xampp/apache/bin

Enjoy!
-Ashton Sanders
(Note: algorytmy.pl was the final website that helped me handle my problem.)

By: Ashton Sanders

SQL Tutorial – Not In

Apr 23 2007

Filed under: SQL

I have an extremely complex SQL database system set up for an email newsletter broadcaster. This piece of code for SQL is what I’ve been looking for for the last week. Here you go:

AND emailaddr NOT IN(SELECT item FROM table WHERE patindex(‘%@%’,emailaddr) > 0 )

That’s all I’m going to say about it right now, look for more SQL Tutorials coming soon.

-Ashton Sanders

RSS

Where Am I?

You have found the semi-coherent ramblings of Ashton Sanders: a website designer, developer and webmaster. This is primarily Ashton's place to save notes about techniques and things that he learns in his never-ending conquest of the internet. Hopefully it's coherent enough to be useful to you too.

Subscribe:

Enter your email to get automatic emails whenever Ashton posts on the blog.

Email:

Advertisers:

Email Marketing $19/Month! OIOpublisher Learn how to make Money from Blogging Hillarious, High-Quality Shirts for $6/each Great Book Keeping and Invoicing Software Advertiser Here

Tags and Categories

Links

Blog Roll