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:
UPDATE p
SET p.[Paid] = '0'
FROM [profiles] p, [registered] r
WHERE p.[ID] = r.[ID] AND r.[subscription_id] = @subscription_id
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:
RESTORE DATABASE dbname
FROM DISK = 'C:\dbname.bak'
WITH REPLACE
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.
Escaping a single quote in MySQL is done by adding a backslash before it (\’)
SELECT 'I went to Kinko\'s'