Bite size SQL

I have a few SQL scripts that I tend to reuse quite often. I will post one or two of these scripts from time to time. Just note that <TABLE>, <COLUMN>, <CONDITION>, <UNIQUE IDENTIFIER COLUMN>, etc. are place holders and must be replaced with a table name, column name and so forth as required.

Insert auto incrementing numbers into a table:

DECLARE @VAL INT;
SET @VAL = 0;
UPDATE <TABLE>
SET @VAL = <COLUMN> = @VAL + 1
WHERE <CONDITION>;

Remove duplicate rows from a table:

DELETE FROM <TABLE>
WHERE <UNIQUE IDENTIFIER COLUMN> NOT IN
(
SELECT MAX (<UNIQUE IDENTIFIER COLUMN>)
FROM <TABLE>
GROUP BY <DUPLICATE COLUMN 1>,<DUPLICATE COLUMN 2>,<DUPLICATE COLUMN n>
)

Bite size SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s