CREATE TABLE Samples
(sample_id CHAR(3) NOT NULL PRIMARY KEY.
col_1 INTEGER NOT NULL
col_2 INTEGER NOT NULL
INSERT INTO Samples (sample_id, col_1, col_2)
col_value ('AAA', 0, 0), ('BBB', 0, 0), ('CCC', 0, 0);
Socko hello hello hello
CREATE TABLE Sample2
(sample_id CHAR(3) NOT NULL,
col_name VARCHAR(10) NOT NULL,
col_col_value INTEGER NOT NULL,
PRIMARY KEY (sample_id, col_name)
INSERT INTO Sample2 (sample_id, col_name, col_value)
VALUES ('AAA', 'col_1', 1), ('AAA', 'col_2', 2);
>> I assume that the problem is due to the fact that the same row is being updated more than once in the same batch.<<
Actually the problems a little more subtle than that. The fiction in ANSI/ISO standard SQL is that when you create an alias (please start explicitly using the AS operator, so your code will be more readable.) It acts as if the result creates a new working table that exist within this scope for the duration of the statement. You get around to reading the SQL standards you learn to hate the word "effectively" which is all over the place. This means that an update statement that has the old Sybase extensions with UPDATE..FROM.. Has a lot of funny scoping rules in it . Add a CTE, and it gets really confusing. This is why I tell people either use a merge statement, or as simple an update as they can write.
As an aside on your code (which is generally good), do you know why we put commas at the start of rows back in the 1960s? We were using punch cards, and this convention made it easy to rearrange and reuse the cards in a deck. Starting sometime in the 1970s, however, we got pretty printers and could reformat our code changed by simply pushing a button. The leading, actually makes the code measurably harder to debug and read because your eyes taught to follow punctuation. If you want some really boring studies on and get them from the University of Maryland.
The IFF function is not part of SQL; we have a CASE expression. You're not using spreadsheets anymore, but make your code look like a mixture of Chinese and English. Try something like this:
SET col_1 = CASE WHEN Sample2.col_name = 'col_1'
THEN Sample2.col_value ELSE col_1 END,
col_2 = CASE WHEN Sample2.col_name = 'col_2'
THEN Sample2.col_value ELSE col_2;
better yet, this is why we have the merge statement. Microsoft is not in a good job of implementing it yet, but it will do a lot of validations, look for multiple updates like you're worried about, etc.
Please post DDL and follow ANSI/ISO standards when asking for help.