Update (SQL)
The UPDATE statement in SQL changes the contents of the entries in a table .
The statement changes all rows of a table or selects a subset of the table based on an additional condition . The application takes place via the column selection of a table ( SET
) in connection with an optional WHERE
condition:
UPDATE
TabellennameSET
Spaltenname = Wert [, Spaltenname = Wert ...] [WHERE
Bedingung]
The use of the UPDATE
statement is restricted to users with authorization to change data ( UPDATE
authorization) for the corresponding table or column. The values to be changed are subject to the restrictions for primary (key) , CHECK
and NOT NULL
.
Examples
Column C1 of table T assumes the value 1 in all rows with value "a" in column C2 :
UPDATE T
SET C1 = 1
WHERE C2 = 'a'
For all rows with the value "a" in column C2 , column C1 takes the value 9 and column C3 takes the value 4:
UPDATE T
SET C1 = 9,
C3 = 4
WHERE C2 = 'a'
Increase the value of column C1 by 1 for all rows with value "a" in column C2 :
UPDATE T
SET C1 = C1 + 1
WHERE C2 = 'a'
Positioning of the string "Text" in front of the value of column C1 , if column C2 contains the value "a":
UPDATE T
SET C1 = 'Text' || C1
WHERE C2 = 'a'
Set the value of column C1 of table T1 to 2, provided that column C2 contains a value from the subset of the WHERE
-condition. The subset contains those values in column C3 of table T2 for which column C4 has the value 0:
UPDATE T1
SET C1 = 2
WHERE C2 IN ( SELECT C3
FROM T2
WHERE C4 = 0)
Set values of multiple columns in a single statement:
UPDATE T
SET C1 = 1,
C2 = 2
Several WHERE
conditions:
UPDATE T
SET A = 1
WHERE C1 = 1
AND C2 = 2
The UPDATE
statement also allows joins , in some database systems even a notation of the SQL standard that FROM
differs from the part :
UPDATE a
SET a.[Update_Spalte] = Update_Wert
FROM Artikel a
JOIN Systematik s
ON a.ArtikelID = s.ArtikelID
WHERE c.classID = 1
The Oracle database system provides another notation (if there is an index for ArticleID) :
UPDATE
(
SELECT *
FROM Artikel
JOIN Systematik
ON Artikel.ArtikelID = Systematik.ArtikelID
WHERE Systematik.classID = 1
)
SET [Update_Spalte] = Update_Wert
In some database systems such as PostgreSQL , the values of the resulting table of a join are defined by one UPDATE
statement per result line.
Risks
-
Halloween problem : In some
Update
statements, the linked indices ofSET
definitions andWHERE
conditions cause (infinite) loops . - An
Update
instruction with a missingWHERE
condition affects all entries in the selected columns.
See also
Individual evidence
- ^ MySQL : UPDATE syntax. Retrieved October 1, 2018 (simplified and translated).
- ^ Tech On The Net: PostgreSQL: FROM Clause. Retrieved October 1, 2018 .
- ^ PostgreSQL : Update. Retrieved November 22, 2018 .