sql

Delete duplicated rows on SQL

Assuming these are the incriminated records                   Let’s narrow down the duplicate ones by grouping the records and removing from the resultset the first valid record and by leaving the duplicates only.               Let’s delete them now.  

SQL Collation conflict

While attempting to copy ¬†records from older database backup I’ve encoutered the following : Msg 468, Level 16, State 9, Line 5 Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation. Possibly caused because the file comes from SQL Server 2008 while now I’m on the 2012.¬† Luckly

SQL- Update one table from another with inner join

To use when there’s the need to update a table with values from another table using a column match (join). UPDATE TABLE1 SET Colum1 = T2.Column1 FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.MatchID = T2.MatchID

SQL – Remove chars from string

To be used when there’s the need or removing one or more chars from a record in a SQL table: UPDATE TableName SET ColumnName = replace( ColumnName, ‘”‘, ” ); — Remove commas UPDATE TableName SET ColumnName = replace( replace(ColumnName, ‘,’, ”), ‘”‘, ” ); — Remove multiple chars: comma and quotes

Trim colums in Sql

Remove white spaces from the beginning and the end of a column : UPDATE TableName SET columnOne = LTRIM(RTRIM(columnOne)), SET columnTwo = LTRIM(RTRIM(columnTwo))

Update da una select

1: UPDATE Table1 SET Field1 = NULL FROM (SELECT TOP 10 FROM Table1 ORDER BY Created DESC) Table1