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 WHEREcondition:

UPDATE Tabellenname SET Spaltenname = Wert [, Spaltenname = Wert ...] [WHERE Bedingung]

The use of the UPDATEstatement is restricted to users with authorization to change data ( UPDATEauthorization) for the corresponding table or column. The values ​​to be changed are subject to the restrictions for primary (key) , CHECKand 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 WHEREconditions:

UPDATE T
   SET A = 1
 WHERE C1 = 1
   AND C2 = 2

The UPDATEstatement also allows joins , in some database systems even a notation of the SQL standard that FROMdiffers 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 UPDATEstatement per result line.

Risks

• Halloween problem : In some Updatestatements, the linked indices of SETdefinitions and WHEREconditions cause (infinite) loops .
• An Updateinstruction with a missing WHEREcondition affects all entries in the selected columns.

See also

• Data manipulation language (DML)
• Consistency (data storage)

Individual evidence

1. ^ MySQL : UPDATE syntax. Retrieved October 1, 2018 (simplified and translated). 
2. ^ Tech On The Net: PostgreSQL: FROM Clause. Retrieved October 1, 2018 . 
3. ^ PostgreSQL : Update. Retrieved November 22, 2018 .