on 2013 Apr 12 6:07 AM
If the insert is not inserting but:
Request clarification before answering.
Hm, as "ON EXISTING" requires a PK lookup, you can only have "collisions" with existing rows if the PK is specified in the INSERT statement itself, and IMHO therefore the PK cannot be the DEFAULT AUTOINCREMENT column here. - So you are using a different column with DEFAULT AUTOINCREMENT?
The docs on the INSERT statement do explain that as follows:
When using the ON EXISTING SKIP and ON EXISTING ERROR clauses, if the table contains default columns, the server computes the default values even for rows that already exist. As a result, default values such as AUTOINCREMENT cause side effects even for skipped rows. In this case of AUTOINCREMENT, this results in skipped values in the AUTOINCREMENT sequence.
So i assume @@IDENTITY would skip over these values as well.
Don't know for ON EXISTING UPDATE - with DEFAULTS OFF, there should not be a need to calculate a fresh value, and for DEFAULTS ON, I'd think it would supply new AUTOINCREMENT values and therefore would naturally increase these values.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
We have sometimes the case, that the PKey is not the autoincrement, but the autoincrement column is used for FKey relations to other tables, because it is more convenient than the PKey, which might consist of multiple columns.
By the way, I wouldn't expect that the autoincrement field is changed implicitly through the on existing update.
By the way, I wouldn't expect that the autoincrement field is changed implicitly through the on existing update.
That is depending on the DEFAULTS option, cf. the docs:
When using the ON EXISTING UPDATE clause with columns that have defaults (including DEFAULT AUTOINCREMENT columns), you can further specify whether to update the column value with the default values by specifying ON EXISTING UPDATE DEFAULTS ON, or leave the column value as it is by specifying ON EXISTING UPDATE DEFAULTS OFF. If nothing is specified, the default behavior is ON EXISTING UPDATE DEFAULTS OFF.
So the "default" (pun intended) here is to leave the field unchanged.
User | Count |
---|---|
75 | |
30 | |
9 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.