How to Unlock your SQL power to the next level- Syntax analysis (Part 1: UPDATE)

Structured Query Language (SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).

In this series of articles, we will study SQL syntax across the multiple platforms. In Part 1, we are reviewing UPDATE operations.

Data

The data we are using is based on SQLite Sample Database Chinook. We create a table called invoices_customer based on invoices, and add a column “Phone” on top of it. The main goal is to update phone number from the table customers.

invoices_customer

customers

SQLite

UPDATE  invoices_customer
SET [Phone] =
    (SELECT [Phone]
     FROM main.customers
     where main.customers.CustomerId = main.invoices_customer.CustomerId 
    )
WHERE CustomerId IN (SELECT CustomerId FROM main.customers);

MS SQL Server

update A
Set A.phone = B.phone
from invoices_customer A
inner join customers B
on A.CustomerId = B.customers.CustomerId;

MS Access

UPDATE invoices_customer
INNER JOIN customers 
ON invoices_customer.CustomerId = customers.CustomerId
SET invoices_customer.Phone = [customers].[Phone];

PostgreSQL

update invoices_customer
Set phone = customers.phone
from  customers
where invoices_customer.CustomerId = customers.CustomerId;

Thank you and more to come