How do I do a JOIN in an UPDATE query with MySQL

As a follow up to my June 22nd post on doing a JOIN in an UPDATE with SQL Server, I just needed to do the same in MySQL. Here’s the syntax I used:

UPDATE `site_locations` 
LEFT JOIN `locations` ON `site_locations`.`location_id` = `locations`.`id`
SET 
`site_locations`.`location_phone` = `locations`.`adwizard_phone`, 
`site_locations`.`location_phone_ext` = `locations`.`adwizard_phone_ext` 
WHERE `site_locations`.`location_phone` IS NULL

As you can see, the order of things is a little different in MySQL vs SQL Server.

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:

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

How To Duplicate a PGSQL database

Recently I needed to create a duplicate of PGSQL database.  Being a MySQL guy, I didn’t know off hand how to do it, but here is an easy way:

pg_dump original_db > original_db.sql
createdb new_db
psql -d new_db < original_db.sql

From a bird’s eye view, you are creating a dump of the original database and outputting it to a file. From there, make sure the new database is created and use your dump file as input for that database. It wouldn’t be too hard to make this into a one-liner, but I like to see intermediate steps to make sure things are going well.

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′]