update still difficult??

Question

Post reply

update still difficult??

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

WonderPra SSCommitted Points: 1624 More actions · Answer 1

Thanks to every one who has been trying to help me...

Lynn, I have to update the column with the primary key not with the identity key...

here is the solution...I just found that there are some triggers running on my table

so used

alter table employees disable trigger all
update employees
set ID = EXTENDEDID
where ID<> EXTENDEDID
alter table employees enable trigger all

--Pra:-):-)--------------------------------------------------------------------------------

demonfox SSCertifiable Points: 6289 More actions · Answer 2

prathibha_aviator (3/14/2013)
well on a sample table yes it works... On my actual production table it wont the errors that i have been showing you are from my production table

Can you post the code for the Procedure trig_AfterFamGroupInsert ?

what exactly it does after the insert or update ??

if the update ends up with the error , it doesn't mean it has caused by this query itself.. may be there are queries depended on insert or update such as trigger ..

well , did you try the code given by Lynn..??

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

demonfox SSCertifiable Points: 6289 More actions · Answer 3

prathibha_aviator (3/14/2013)
Thanks to every one who has been trying to help me...
Lynn, I have to update the column with the primary key not with the identity key...
here is the solution...I just found that there are some triggers running on my table
so used
alter table employees disable trigger all
update employees
set ID = EXTENDEDID
where ID<> EXTENDEDID
alter table employees enable trigger all

Bingo !!! :w00t:

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

prathibha_aviator (3/14/2013)
Thanks to every one who has been trying to help me...
Lynn, I have to update the column with the primary key not with the identity key...
here is the solution...I just found that there are some triggers running on my table
so used
alter table employees disable trigger all
update employees
set ID = EXTENDEDID
where ID<> EXTENDEDID
alter table employees enable trigger all

I guess I'm confused. Your SET statement: SET ID = EXTENDEDID; my SET statement: SET ID = EXTENDEDID;.

Am I missing something here? They look the same to me.

You'd still have to disable and then enable the triggers to use my code.

WonderPra SSCommitted Points: 1624 More actions · Answer 5

Lynn 🙂

Haha I confused you with my bad english....or may i did not understand this statement

set identity_insert dbo.FamGroup_Lookup off;

go

But when I used your query on my database, its gives me the same error.

(65 row(s) affected)
Msg 512, Level 16, State 1, Procedure trig_AfterFamGroupInsert, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
(19 row(s) affected)

--Pra:-):-)--------------------------------------------------------------------------------

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

prathibha_aviator (3/14/2013)
Lynn 🙂
Haha I confused you with my bad english....or may i did not understand this statement
set identity_insert dbo.FamGroup_Lookup off;
go
But when I used your query on my database, its gives me the same error.
(65 row(s) affected)
Msg 512, Level 16, State 1, Procedure trig_AfterFamGroupInsert, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
(19 row(s) affected)

Does it work when you disable the triggers on the table?

As for this: set identity_insert dbo.employees off;, you did see the corresponding set identity_insert dbo.employees on; earlier when I was inserting your test data into the table dbo.employees that you provided. Had to do that in order to insert the values for the identity column that you provided in your sample data.

WonderPra SSCommitted Points: 1624 More actions · Answer 7

Yes it did... Thanks Again Lynn 🙂

--Pra:-):-)--------------------------------------------------------------------------------

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 8

prathibha_aviator (3/14/2013)
Yes it did... Thanks Again Lynn 🙂

Great that you were able to solve the problem temporarily by disabling the trigger.

If you want to avoid such issues in future, you will have to check the code in the trigger and fix it.

I would suggest you to fix the trigger rather than disabling the trigger for any updates.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

sqlgirlatty SSC Veteran Points: 250 More actions · Answer 9

Have you tried:

Update employees

SET ID = e.EXTENDEDID

FROM employees e

WHERE ID <> e.EXTENDEDID

WonderPra SSCommitted Points: 1624 More actions · Answer 10

sqlgirlatty (3/15/2013)
Have you tried:
Update employees
SET ID = e.EXTENDEDID
FROM employees e
WHERE ID <> e.EXTENDEDID

Yah We have been doing the same code in different ways all the tymm... Until we found its a trigger Issue.. Thanks Though....

Kingston Dhasian (3/15/2013)
prathibha_aviator (3/14/2013)
Yes it did... Thanks Again Lynn 🙂
Great that you were able to solve the problem temporarily by disabling the trigger.
If you want to avoid such issues in future, you will have to check the code in the trigger and fix it.
I would suggest you to fix the trigger rather than disabling the trigger for any updates.

Yes..I agree and we decided to take it to the notice.... In this case we cant bulk update or bulk insert because of the triggers....Thanks for the suggestion though

--Pra:-):-)--------------------------------------------------------------------------------