17:33

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

SQL data manipulation language

Data modification statements

UPDATE statement page 2

Transact-SQL UPDATE statement extends the Standard at the cost of using the optional FROM clause. This clause specifies a table that is used to provide the criteria for the update operation. This extension gives additional flexibility specifying a join that can be used instead of a subquery in the WHERE clause to identify rows to be updated.

Example 6.2.1

Let us write "No PC" in the type column for those PC models in the Product table that have not corresponding rows in the PC table.

The solution through table join may be written as:

UPDATE Product
SET type = 'No PC'
FROM Product pr LEFT JOIN 
 PC ON pr.model=PC.model 
WHERE type = 'pc' AND 
 PC.model IS NULL;

Here, we use outer join that results in the pc.model column contains NULL values for those PC models that are absent from the PC table, which is used for providing the criteria for the update operation. Clearly, this task has also a "standard" solution:

UPDATE Product
SET type = 'No PC'
WHERE type = 'pc' AND 
 model NOT IN (SELECT model 
 FROM PC
 );

MySQL

The MySQL UPDATE statement also has functionality similar to the optional FROM clause in SQL Server. But instead of FROM clause, the tables are joined directly in the UPDATE clause. Example 6.1.2 in MySQL syntax can be rewritten as follows

UPDATE Product pr 
    LEFT JOIN PC ON pr.model=PC.model 
    SET type = 'No PC'
    WHERE type = 'pc' 
    AND PC.model IS NULL;

Suggested exercises: 7, 9, 12, 15, 17, 20

Pages

FROM UPDATE Transact-SQL

Previous [Table-values constructor]

[DELETE statement] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

Как изготовить компрессор своими руками для аэрографа?

SQL Problems and solutions

S. I. Moiseenko

SQL data manipulation language

Data modification statements

UPDATE statement page 2

MySQL