How do I do a JOIN in an UPDATE with SQL Server

I’m working on some client work on Classic ASP and SQL Server.  I needed to do an UPDATE with a JOIN and ran across an issue.  As with most things T-SQL, the syntax differs from the ANSI standard. Here’s how to do it:

SET p.[Paid] = '0'
FROM [profiles] p, [registered] r
WHERE p.[ID] = r.[ID] AND r.[subscription_id] = @subscription_id

SQL Server Error 3154

I ran accross the SQL Server error 3154. In SQL Server 2008, it says “Restore Failed for Server ‘ServerName\InstanceName'” Additional Information: “System.Data.SQLClient.SqlError: The backup set holds a backup of a database other than the existing ‘dbname’ database. (Microsoft.SqlServer.Smo)”


Use WITH REPLACE.  This can be found in the GUI tool or done in T-SQL as follows:

FROM DISK = 'C:\dbname.bak'

Escaping Single Quotes in MS SQL Server (T-SQL)

Escaping single quotes (‘) in Transact-SQL is done by replacing the single quote with two single quotes. So:

SELECT 'I went to Kinko''s'

Returns “I went to Kinko’s”

The only case where two consecutive single quotes don’t return a single quote is when the first one also starts the string.  In this case, if only two single quotes are present, the second one ends the string and the return value is the empty string.  SQL Blog Casts has a good example of this.

Doing the same in MySQL

Escaping a single quote in MySQL is done by adding a backslash before it (\’)

SELECT 'I went to Kinko\'s'
[cta id=’1682′]