Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Attempt to clarify the scope of when UPSERT processing applies. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | branch-3.28 |
| Files: | files | file ages | folders |
| SHA3-256: |
aae75b6ac27f4553720bfc6464f3772a |
| User & Date: | drh 2019-05-02 16:25:02 |
Context
|
2019-05-10
| ||
| 11:38 | Fix a typo in the documentation for ANALYZE. (check-in: 0917d5f9d6 user: drh tags: branch-3.28) | |
|
2019-05-02
| ||
| 16:25 | Attempt to clarify the scope of when UPSERT processing applies. (check-in: aae75b6ac2 user: drh tags: branch-3.28) | |
|
2019-04-30
| ||
| 20:37 | More "whynotgit.html" updates. (check-in: 71cccf3d65 user: drh tags: branch-3.28) | |
Changes
Changes to pages/lang.in.
3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 3489 3490 3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 |
<p>An UPSERT is an ordinary [INSERT] statement that is followed by the special ON CONFLICT clause shown above. <p>The syntax that occurs in between the "ON CONFLICT" and "DO" keywords is called the "conflict target". The conflict target specifies a specific uniqueness constraint that will trigger the upsert. The conflict target is required for DO UPDATE upserts, but is optional for DO NOTHING. A DO NOTHING upsert without a conflict target works the same as an [ON CONFLICT|INSERT OR IGNORE]. <p>If the insert operation would cause the uniqueness constraint identified by the conflict-target clause to fail, then the insert is omitted and either the DO NOTHING or DO UPDATE operation is performed instead. In the case of a multi-row insert, this decision is made separately for each row of the insert. <p>Column names in the expressions of a DO UPDATE refer to the original unchanged value of the column, before the attempted INSERT. To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name. <p>Some examples will help illustrate the difference: |
| | | > > > > > > > |
3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 3489 3490 3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 3501 3502 3503 3504 3505 3506 3507 |
<p>An UPSERT is an ordinary [INSERT] statement that is followed by the special ON CONFLICT clause shown above. <p>The syntax that occurs in between the "ON CONFLICT" and "DO" keywords is called the "conflict target". The conflict target specifies a specific uniqueness constraint that will trigger the upsert. The conflict target is required for DO UPDATE upserts, but is optional for DO NOTHING. When the conflict target is omitted, the upsert behavior is triggered by a violation of any uniqueness constraint on the table of the INSERT. <p>If the insert operation would cause the uniqueness constraint identified by the conflict-target clause to fail, then the insert is omitted and either the DO NOTHING or DO UPDATE operation is performed instead. In the case of a multi-row insert, this decision is made separately for each row of the insert. <p>The special UPSERT processing happens only for uniqueness constraint on the table that is receiving the INSERT. A "uniqueness constraint" is an explicit UNIQUE or PRIMARY KEY constraint within the CREATE TABLE statement, or a [unique index]. UPSERT does not intervene for failed NOT NULL or foreign key constraints or for constraints that are implemented using triggers. <p>Column names in the expressions of a DO UPDATE refer to the original unchanged value of the column, before the attempted INSERT. To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name. <p>Some examples will help illustrate the difference: |