MSSQL Tutorial: Delete All But 1 Duplicate Records

Leave a comment Standard

declare @id int, @total int

/* SELECT COUNT OF POTENTIAL DUPLICATES */
select id, count(id) from [tablename]
where [parameters] group by [column name]

/* LOOP TO CLEAN UP THE DUPLICATES */
declare aff_crsr cursor LOCAL static for
select id, count(id) from [tablename]
where [parameters] group by id

open loop_crsr
fetch from loop_crsr into @id, @total

while @@fetch_status = 0 begin
print cast(@id as varchar) + ‘ – ‘ + cast(@total as varchar) + ‘ – ‘ + cast((@total – 1) as varchar)

delete top (@total-1) from [tablename] where id=@id and [parameters]

fetch from loop_crsr into @id, @total

Advertisements

SQL Tutorial: Select, Update & Delete Syntax

Leave a comment Standard

Select Statements

General Syntax

  • SELECT [column], [column], [column] FROM [tablename] WHERE [column] = [value]

Select All Columns

  • SELECT * FROM [tablename]

Limit # of Records Returned – MSSQL

  • SELECT TOP [number] * FROM [tablename]

Limit # of Records Returned – SQL

  • SELECT * FROM [tablename] LIMIT [# from], [# to]
    • this one works well for pagination. You can select results 0 – 30, then 31 to 60, etc.
  • SELECT * FROM [tablename] LIMIT [number]
    • use this one if you just want to cut off the number of records returned

Sum A Field By Status ID

  • SELECT sum([column]), status_id FROM [tablename] WHERE [column] = [value] GROUP BY status_id

Count A Field

  • SELECT count([column]) FROM [tablename] WHERE [column] = [value]

Sort By A Field Ascending or Descending

  • SELECT [column], [column] FROM [tablename] WHERE [column] = [value] ORDER BY [column] DESC, [column] ASC

Complex Where Values

  • SELECT [column], [column] FROM [tablename] WHERE ([column] = [value] AND [column] = [value]) OR ([column] = [value] AND [column] = [value])

Select From Result Subset

  • SELECT [column], [column] FROM [tablename] WHERE [column] IN ([value], [value], [value])

Select From Select Subset

  • SELECT [column] FROM [tablename] WHERE [column] IN (SELECT [column] FROM [tablename])

Inner Join Select All – records must exist in table B and table A matching on conditions

  • SELECT * FROM [tablename] A INNER JOIN [tablename] B ON A.[column] = B.column WHERE A.[column] = [value]

Left Join Select All – records must exist in table A

  • SELECT * FROM [tablename] A LEFT OUTER JOIN [tablename] B on A.[column] = B.[column] WHERE B.[column] = [value]

Inner Join Select All From One Table, Select Fields From Another

  • SELECT A.*, B.[column], B.[column] FROM [tablename] A INNER JOIN [tablename] B ON A.[column] = B.column WHERE A.[column] = [value]

Unions – results and columns must match in both SQL statements

  • SELECT A.*, B.[column], B.[column] FROM [tablename] A INNER JOIN [tablename] B ON A.[column] = B.column WHERE A.[column] = [value] UNION SELECT C.*, D.[column], D.[column] FROM [tablename] C INNER JOIN [tablename] D ON C.[column] = D.column WHERE C.[column] = [value]

Delete Statements

Always do a select statement first to make sure the records you’re about to delete are the ones you want. Also consider any triggers that may be fired as a result of deleting these records.

General Syntax

  • DELETE FROM [tablename] WHERE [column] = [value]

Check Yourself

  • SELECT * FROM [tablename] WHERE [column] = [value]
  • Now replace the text in red with the text in blue below to delete the records you found
  • DELETE FROM [tablename] WHERE [column] = [value]

Update Statements

Make sure you do a select statement before attempting to update records to ensure you only update the records you intended. Also consider any triggers that may be fired as a result of updating these records.

General Syntax

  • UPDATE [tablename] SET [column]=[value], [column]=[value], [column]=[value] WHERE [column]=[value]

Check Yourself

  • SELECT * FROM [tablename] WHERE [column] = [value]
  • Replace the blue section with what’s in red below, then add the columns and values you want to update as seen in green below
  • UPDATE [tablename] SET [column]=[value], [column]=[value], [column]=[value] WHERE [column]=[value]

MySQL & MSSQL Tutorial: Combine Multiple Rows Into A Single, Comma Delineated List

Leave a comment Standard

In MySQL with PHP:

<?
$delimiter = ", ";
$count = 0;

$loop = mysql_query("SELECT S.suffix FROM people P
          INNER JOIN surnames S on P.suffix_id = S.id")
          or die ('cannot select merged data');

while ($row = mysql_fetch_array($loop))
{
    $merged_data .= $row['suffix'];

    if ($count+1 < mysql_num_rows($loop))
       $merged_data .= $delimiter;

    $count++;
}

echo $merged_data;
?>

In MSSQL:

declare @merged_data varchar(2000), @delimiter char(1)  --declare your variable

set @delimiter = ', ' --whatever you want to separate the field values

--join two tables and select the fields you want to merge into a single field
 select @merged_data = isNull(@merged_data + @delimiter, '') + S.suffix
 from people P inner join surnames S on P.suffix_id = S.id

 select @merged_data

MSSQL Tutorial: Search code inside of stored proceedures

Leave a comment Standard

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = ‘expiration_date =’, @notcontain = ”

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case
when sysobjects.xtype = ‘P’ then ‘Stored Proc’
when sysobjects.xtype = ‘TF’ then ‘Function’
when sysobjects.xtype = ‘TR’ then ‘Trigger’
end as [Object Type]

FROM sysobjects,syscomments

WHERE sysobjects.id = syscomments.id AND sysobjects.type in (‘P’,’TF’,’TR’) AND sysobjects.category = 0 AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0 AND ((CHARINDEX(@notcontain,syscomments.text)=0 or CHARINDEX(@notcontain,syscomments.text)<>0))

Sorry, this little piece of handiwork isn’t my own. I got it from select-sql.com but I thought I would share the love. You can use this to search through the code inside of your stored proceedures to find whatever it is you’re looking for.