Geek Answers Handbook

What exactly means "(affected lines (lines)")?

I performed a simple request:

UPDATE table

SET user_id = '123456'

WHERE user_id = '234567'

Then I got an error message:

(2942 row(s) affected)
Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'PK__users__6B24EA82'. Cannot insert duplicate key in object 'dbo.users'. The duplicate key value is (123456).
The statement has been terminated.

Does this mean that I really made changes to 2942 lines? This is not true. I was not very lucky in my search. Any help is appreciated.

+4
source share
5 answers

No, this means that you would make changes to 2942 lines, except that one or more of them violates the PRIMARY KEY constraint so that they are thrown back.

+5
source

Assuming what user_idis the primary key (which offers an error message based on the value in the error message), the request:

UPDATE table
    SET user_id = '123456'
    WHERE user_id = '234567';

table. user_id - , . where .

, . , 2942 . , , , .

, , user_id = '123456', .

(: . , . , .)

+5

user_id = '234567' PK , , 2943, .

,

, , Msg 2627

+2

- pk u

+2

trigger , - , trigger . instead of update trigger:

CREATE TABLE [dbo].[Table_1](
    [ID] [int] NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
))
go

CREATE TABLE [dbo].[Table_2](
    [ID] [int] NOT NULL)
go

insert into Table_1 values(1),(2),(3),(4)
update Table_1 set ID = 2 where ID = 1

Msg 2627, 14, 1, 1
" PK_Table_1 ". 'Dbo.Table_1.
.

create trigger [tr_Table_1] on [Table_1]
instead of update 
as 
begin
    insert into [dbo].[Table_2]
    select * from [dbo].[Table_1]
    update Table_1 set ID = 2 where ID = 1
end
go

update Table_1 set ID = 2 where ID = 1

(4 lines affected)
Msg 2627, level 14, state 1, procedure tr_Table_1, line 8
Violation of the PRIMARY KEY restriction "PK_Table_1". Cannot insert duplicate key into object "dbo.Table_1".
The statement was discontinued.

+1
source

More articles:


All Articles