March 13, 2013 at 10:05 pm
I have a table which has three columns
create table employees
(
ID int NOT NULL PRIMARY KEY
NAME varchar(10) NULL
EXTENDEDID int NOT NULL IDENTITY(1,1)
)
INSERT INTO employees ( ID, NAME, EXTENDEDID) VALUES
( 1, Sean, 1), ( 3, Jason, 2), ( 2, Gail, 3), ( 5, Jeff, 4)
I have to Identify those columns where ID is not equal to EXTENDEDID
So I did
Select * from employees where ID <> EXTENDEDID
Now I have to make the two columns equal.. I know that EXTENDEDID is an identity column and i cant change it..So trying to make the columns equal comparing with EXTENDEDID
So the required output shd be
ID NAME EXTENDEDID
1 Sean 1
2 Jason 2
3 Gail 3
4 Jeff 4
Now I used the following statement/statements to change get the O/P
Update employees set ID= (select EXTENDEDID from employees where ID<> EXTENDEDID) where ID<> EXTENDEDID
update employees set ID= EXTENDEDID
Update employees SET ID= EXTENDEDID WHERE employees.ID <> employees.EXTENDEDID
All of them fail
My next requirement is to
Print all those records that have been updated accordingly...
Any Suggestions are appreciated 🙂
--Pra:-):-)--------------------------------------------------------------------------------
March 13, 2013 at 11:22 pm
Hi)
As you said you have to make two columns equal to each other.
Your solution:
Update employees SET ID= EXTENDEDID WHERE employees.ID <> employees.EXTENDEDID
should not fail. And it does actually what you want to get.
Could you specify what is the second requirement:
do you want to get all rows that are going to be updated after running update script?
March 13, 2013 at 11:47 pm
My first question is Why you need this ? means updatign the ID column with ExtendedID
Second,whatever the reason , there are very much chances that you wil get PK violation error , another tyhings it can disturb the FK relation too with other tables
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 14, 2013 at 12:10 am
prathibha_aviator (3/13/2013)
create table employees
(
ID int NOT NULL PRIMARY KEY
NAME varchar(10) NULL
EXTENDEDID int NOT NULL IDENTITY(1,1)
)
INSERT INTO employees ( ID, NAME, EXTENDEDID) VALUES
( 1, Sean, 1), ( 3, Jason, 2), ( 2, Gail, 3), ( 5, Jeff, 4)
I have to Identify those columns where ID is not equal to EXTENDEDID
So I did
Select * from employees where ID <> EXTENDEDID
Now I have to make the two columns equal.. I know that EXTENDEDID is an identity column and i cant change it..So trying to make the columns equal comparing with EXTENDEDID
So the required output shd be
ID NAME EXTENDEDID
1 Sean 1
2 Jason 2
3 Gail 3
4 Jeff 4
Now I used the following statement/statements to change get the O/P
Update employees set ID= (select EXTENDEDID from employees where ID<> EXTENDEDID) where ID<> EXTENDEDID
update employees set ID= EXTENDEDID
Update employees SET ID= EXTENDEDID WHERE employees.ID <> employees.EXTENDEDID
All of them fail
My next requirement is to
Print all those records that have been updated accordingly...
Any Suggestions are appreciated 🙂
I tend to agree with Bhuvnesh. You may mess your FK relationship. You cannot simply update your PK if its refereed in other tables.
March 14, 2013 at 12:58 am
--This will fail as the sub query will probably return more than one rows
Update employees set ID= (select EXTENDEDID from employees where ID<> EXTENDEDID) where ID<> EXTENDEDID
--Below queries should not fail
update employees set ID= EXTENDEDID
Update employees SET ID= EXTENDEDID WHERE employees.ID <> employees.EXTENDEDID
What are the errors/problems you are facing when you are running the query?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 14, 2013 at 3:02 am
What are the errors/problems you are facing when you are running the query?
Also,
out of curisoity , why do you have this table structure anyway ..
ID column with int , which actually holds the primary key... and then another externalid column with Identity , .. So, by identity , I think , since it autogenerate , you shouldn't be keeping a foreign key on this one ..
Moreover, you need to keep in mind about the source of data for ID column .. or you might end up with primary key violation error while updating ID with ExternalID ... as mentioned by bhuvnesh
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
March 14, 2013 at 8:00 am
@ above all
Well, its just an example... My work place might have had really bad designers when they started designing the database... That Primary key column(ID) is not used in any of the tables... But EXTENDEDID is used as a foreignkey to many tables in the database.. 🙂
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.
This is the error i get
--Pra:-):-)--------------------------------------------------------------------------------
March 14, 2013 at 8:19 am
Update employees set ID= (select EXTENDEDID from employees where ID <> EXTENDEDID) where ID <> EXTENDEDID
The query above is probably giving the error you mentioned. This is because the subquery returns more than one value.
Try executing the subquery below and see the number of rows you get. You will get more than one rows.
select EXTENDEDID from employees where ID <> EXTENDEDID
What is the error messages you get while executing the other 2 queries mentioned below. I think they should work.
update employees set ID= EXTENDEDID
Update employees SET ID= EXTENDEDID WHERE employees.ID <> employees.EXTENDEDID
Edit:Edited the comments
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 14, 2013 at 9:08 am
prathibha_aviator (3/14/2013)
@ above allWell, its just an example... My work place might have had really bad designers when they started designing the database... That Primary key column(ID) is not used in any of the tables... But EXTENDEDID is used as a foreignkey to many tables in the database.. 🙂
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.
This is the error i get
They must have designed it for a reason ; either you don't know why they did it or they didn't why they were doing it .. former is better for an organisation 😛
Coming back to the point ;
The ExtendedId can't be referenced from any table .. since it's not a primarykey .. no table can reference it ; that aside , it's hard to keep up with identity column to have reference from any other table ..
about the update , try this ..
UPDATE d
SET d.id = e.extendedid
FROM employees d
INNER JOIN employees e
ON d.extendedid = e.extendedid
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
March 14, 2013 at 9:13 am
Infact the same error for all of them like i mentioned before...
This is the statement
update employees set ID = EXTENDEDID
I get
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.
Now I am confused how do i loop through the result set of select statement one and by one and update it...
it might be a good idea
like
if i run
select * from employees where ID <> EXTENDEDID and store the results in temp table
while loop < select COUNT(*) from temptable + 1
BEGIN
ANY SUGGESTIONS HERE?END
--Pra:-):-)--------------------------------------------------------------------------------
March 14, 2013 at 9:16 am
Did you try the above query ???
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
March 14, 2013 at 9:29 am
prathibha_aviator (3/14/2013)
Infact the same error for all of them like i mentioned before...This is the statement
update employees set ID = EXTENDEDID
I get
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.
Now I am confused how do i loop through the result set of select statement one and by one and update it...
it might be a good idea
this shouldn't give the error,what u just mentioned..
it is a row by row process...
same as
UPDATE employees
SET id = extendedid
WHERE id <> extendedid
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
March 14, 2013 at 10:02 am
UPDATE employees
SET ID= EXTENDEDID
WHERE ID<> EXTENDEDID
Same Error
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.
--Pra:-):-)--------------------------------------------------------------------------------
March 14, 2013 at 10:13 am
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
--Pra:-):-)--------------------------------------------------------------------------------
March 14, 2013 at 10:22 am
How about this:
create table dbo.employees
(
ID int NOT NULL PRIMARY KEY,
NAME varchar(10) NULL,
EXTENDEDID int NOT NULL IDENTITY(1,1)
)
set identity_insert dbo.employees on;
INSERT INTO employees ( ID, NAME, EXTENDEDID) VALUES
( 1, 'Sean', 1), ( 3, 'Jason', 2), ( 2, 'Gail', 3), ( 5, 'Jeff', 4)
set identity_insert dbo.employees off;
go
select * from dbo.employees;
go
with MisMatchIDs as (
select
ID,
Name,
EXTENDEDID
from
dbo.employees
where
ID <> EXTENDEDID
)
update MisMatchIDs set
ID = EXTENDEDID;
go
select * from dbo.employees;
go
drop table dbo.employees;
go
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply