By Programmer For Programmer

Background

So at my current job at jamasoftware.com, our Contour application supports the three major databases MySQL, SQL Server, and Oracle (sorry PostgreSQL fans… maybe soon).  Recently I needed to run UPDATE statements with JOINs, and had to learn the different syntax for each of these databases.  I thought I would share all of them side-by-side, so that you can easily see the differences:

Our Simplified Schema:

Table: ORGANIZATION
id int primary key

Table: PROJECT
id int primary key
organizationId int foreign key ORGANIZATION(id)

Table: DOCUMENT
id int primary key
projectId int foreign key ORGANIZATION(id)
organizationId int null

The Task

Update all DOCUMENT records with the organizationId of their corresponding project

The Solution

MySQL:
UPDATE DOCUMENT d
    INNER JOIN PROJECT p ON d.projectId = p.id
SET d.organizationId = p.organizationId
WHERE d.organizationId IS NULL;

SQL Server:
UPDATE d
SET d.organizationId = p.organizationId
FROM DOCUMENT d
    INNER JOIN PROJECT p ON d.projectId = p.id
WHERE d.organizationId IS NULL

Oracle:
UPDATE (SELECT d.organizationId AS docOrgId, p.organizationId AS projectOrgId
        FROM DOCUMENT d
            INNER JOIN PROJECT p ON d.projectId = p.id
        WHERE d.organizationId IS NULL) v
set v.docOrgId = v.projectOrgId