Can an IDENTITY column be updated?

(SQL Server 2000, SP3)
Hello all!
I have a simple table:
create table Test (Id int identity(1, 1) not NULL, Name varchar(255) NULL)
insert into Test (Name) values ('Test')
And I'd like to potentially update the IDENTITY column Id:
update Test set Id = 100 where Id = 1
However, I get the following error:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'Id'.
Even if I try to "wrap" the UPDATE in a "set identity_insert", I still get the same error.
Is there any way to update a column with an IDENTITY property?
Thanks!
John PetersonThanks Sue! Ah, I see the blurb in BOL that says an IDENTITY can't be updated. Bummer.
:-(
It seems to me that in older versions of SQL Server, one could update a column with the
IDENTITY property. But, no longer (or my memory isn't what it once was ;-).
Thanks again!
John Peterson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:dc1njvsscenku5aoklp93tl6rjptcibdcf@.4ax.com...
> Identity columns can't be updated - I think it's documented
> under the UPDATE topic in BOL T-SQL reference. One possible
> option would be to set identity_insert on, use the existing
> values for a new record and insert the new record with the
> identity value you need to use and then delete the old
> record.
> -Sue
> On Wed, 13 Aug 2003 20:10:46 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3)
> >
> >Hello all!
> >
> >I have a simple table:
> >
> >create table Test (Id int identity(1, 1) not NULL, Name varchar(255) NULL)
> >insert into Test (Name) values ('Test')
> >
> >And I'd like to potentially update the IDENTITY column Id:
> >
> >update Test set Id = 100 where Id = 1
> >
> >However, I get the following error:
> >
> >Server: Msg 8102, Level 16, State 1, Line 1
> >Cannot update identity column 'Id'.
> >
> >Even if I try to "wrap" the UPDATE in a "set identity_insert", I still get the same
error.
> >
> >Is there any way to update a column with an IDENTITY property?
> >
> >Thanks!
> >
> >John Peterson
> >
>

can a SELECT return this result ?!

Hello, this is a table fragment - Table1
Voucher (Int) , RNo (Varchar(20) , Amount (int)
this is a data example
Voucher , RNo , Amount
--
1 , R1 , 100
2 , R1 , -100
3 , R1 , 100
4 , R1 , 50
5 , R1 , 25
6 , R2 , 30
7, R2 , 20
--
now i need to select rows for all amounts in same RNo that does not have
corresponsing amount in opposite sign, like that
Result needed
Voucher , RNo , Amount
--
3, R1 , 100
4, R1 , 50
5, R1 , 25
6 , R2 , 30
7, R1 , 20
--
the first 2 rows have same RNo='R1' and they have same value but opposite
sign 100 , -100 so they will make each other disappear
the third row with voucher=3 then does not find corresponding -100 for same
RNo because it disappeared in first comparison.
i hope im clear but i can't figure out a way to select this retult rather
than using a cursor ! any help
Thank you
BassamDo:
SELECT MAX( voucher ), RNo, SUM( Amount )
FROM tbl
GROUP BY RNo, ABS( Amount ) ;
Anith|||Hi Anith,
This Query wont work for the following set of data
1 r1 100
2 r1 100
3 r1 -100
4 r1 50
5 r1 25
6 r2 30
7 r2 20
Your Query fetches voucher No 3 with values as +100 , this row is not there
in the table at all.
the right result would be to fetch voucher 1 with amount 100.
The following Query will work out for all cases
select * from vouch
where amt > 0 and voucher not in
(
select
min(v2.voucher)
from
vouch v1
inner join vouch v2 on
v1.rno = v2.rno and
abs(v1.amt) = v2.amt and
v1.voucher > v2.voucher
where v1.amt < 0
)
- Sha Anand
"Anith Sen" wrote:

> Do:
> SELECT MAX( voucher ), RNo, SUM( Amount )
> FROM tbl
> GROUP BY RNo, ABS( Amount ) ;
> --
> Anith
>
>|||>> Your Query fetches voucher No 3 with values as +100 , this row is not
Based on the OP's narrative, it is not clear if the row with voucher 1 or
voucher 2 is the "corresponding" row for the one with voucher 3. In any
case, considering any row with a positive amount value, the query can be
changed to:
SELECT MAX( CASE WHEN SIGN(Amount) <> -1. THEN voucher END )
..
Anith|||Anith,

> SELECT MAX( CASE WHEN SIGN(Amount) <> -1. THEN voucher END )
> ...
how about this data?
1 r1 100
2 r1 100
3 r1 -100
4 r1 100
5 r1 100
what is the correct output?|||Consider this set of data:
INSERT INTO Table1 (Voucher, RNo, Amount)
SELECT 1 , 'R1' , 100 UNION ALL
SELECT 2 , 'R1' ,-100 UNION ALL
SELECT 3 , 'R1' , 100 UNION ALL
SELECT 21 , 'R1' ,-100 UNION ALL
SELECT 31 , 'R1' , 100 UNION ALL
SELECT 4 , 'R1' , 50 UNION ALL
SELECT 5 , 'R1' , 25 UNION ALL
SELECT 6 , 'R2' , 30 UNION ALL
SELECT 61 , 'R2' , 30 UNION ALL
SELECT 62 , 'R2' , 30 UNION ALL
SELECT 7 , 'R2' , 20 ;
go
your query returns:
select * from table1
where amount > 0 and voucher not in
(
select
min(v2.voucher)
from
table1 v1
inner join table1 v2 on
v1.rno = v2.rno and
abs(v1.amount) = v2.amount and
v1.voucher > v2.voucher
where v1.amount < 0
)
Voucher RNo Amount
-- -- --
3 R1 100
4 R1 50
5 R1 25
6 R2 30
7 R2 20
31 R1 100
61 R2 30
62 R2 30
(8 row(s) affected)
I think it should return only 1 row for the amount of 100|||Argh...
; WITH cte AS (
SELECT Voucher, RNo, Amount,
RANK() OVER ( PARTITION BY SIGN( Amount )
ORDER BY RNo, voucher ) AS "rank"
FROM tbl )
SELECT Voucher, RNo, Amount
FROM cte c1
WHERE ( SELECT COUNT(*) FROM cte c2
WHERE c2.rank = c1.rank ) = 1 ;
Anith|||Consider this data:
delete from Table1;
INSERT INTO Table1 (Voucher, RNo, Amount)
SELECT 101 , 'R1' , 100 UNION ALL
SELECT 2 , 'R1' ,-100 UNION ALL
SELECT 3 , 'R1' , 100 UNION ALL
SELECT 12 , 'R1' ,-100 UNION ALL
SELECT 13 , 'R1' , 100 UNION ALL
SELECT 4 , 'R1' , 50 UNION ALL
SELECT 5 , 'R1' , 25 UNION ALL
SELECT 6 , 'R2' , 30 UNION ALL
SELECT 61 , 'R2' , 30 UNION ALL
SELECT 62 , 'R2' , 30 UNION ALL
SELECT 7 , 'R2' , 20 ;
I ran this:
WITH cte AS (
SELECT Voucher, RNo, Amount,
RANK() OVER ( PARTITION BY SIGN( Amount )
ORDER BY RNo, voucher ) AS "rank"
FROM table1 )
SELECT Voucher, RNo, Amount
FROM cte c1
WHERE ( SELECT COUNT(*) FROM cte c2
WHERE c2.rank = c1.rank ) = 1 ;
and got this:
Voucher RNo Amount
-- -- --
5 R1 25
13 R1 100
101 R1 100
6 R2 30
7 R2 20
61 R2 30
62 R2 30
(7 row(s) affected)
Note that 50 is missing and 100 is twice, there should be 100 only
once.
I tweaked your query as follows:
WITH cte AS (
SELECT Voucher, RNo, Amount,
RANK() OVER ( PARTITION BY Amount
ORDER BY RNo, voucher ) AS "rank"
FROM table1 )
SELECT Voucher, RNo, Amount
FROM cte c1
WHERE ( SELECT COUNT(*) FROM cte c2
WHERE c2.rank = c1.rank and c2.amount = -c1.amount) = 0 ;
and got the results which I think are correct:
Voucher RNo Amount
-- -- --
7 R2 20
5 R1 25
6 R2 30
61 R2 30
62 R2 30
4 R1 50
101 R1 100
(7 row(s) affected)
What do you think?|||Alexander Kuznetsov wrote:

> WITH cte AS (
> SELECT Voucher, RNo, Amount,
> RANK() OVER ( PARTITION BY Amount
> ORDER BY RNo, voucher ) AS "rank"
> FROM table1 )
> SELECT Voucher, RNo, Amount
> FROM cte c1
> WHERE ( SELECT COUNT(*) FROM cte c2
> WHERE c2.rank = c1.rank and c2.amount = -c1.amount) = 0 ;
This is another version using row_number (it's easier to understand for me):
WITH cte AS
(
SELECT
Voucher, RNo, Amount,
ROW_NUMBER() OVER ( PARTITION BY RNo, Amount
ORDER BY voucher ) AS rn
FROM table1
)
SELECT c1.Voucher, c1.RNo, c1.Amount
FROM cte c1
WHERE NOT EXISTS
(
SELECT * FROM cte c2
WHERE c2.RNo = c1.RNo AND c2.amount = -c1.amount AND c2.rn = c1.rn
);
Btw, if MS implemented EXCEPT ALL, this would be so simple:
select RNo, amount from table1 where amount >= 0
except all
select RNo, -amount from table1 where amount < 0
The only divantage is the missing RNo...
Dieter|||One more approach is to use sum() over() OLAP function, but I don't
think it is available in SS2005 yet (it sure would work in Oracle 9i
and higher). Anyway, try someting like this (untested):
select * from(
select ..., sum() over(partition by rno, abs(amount) order by amount)
rolling_total
FROM table1) t
where rolling_total>0
It is amazing how powerful and useful are OLAP functions, once you get
used to them!

Can a column data type be changed on a replicated table?

On sqlserver 2000 transactional replication:

How would I best go about changing a published table's column from smallint to int? I could not find anything about it in BOL or MS.com. I do not think EM/Replication Properties allows the change. I suspect I have to run "Alter Table/Column" on the Publisher and each Subscriber the old-fashioned way. Is that true?

Thanks!

In SQL 2000, the only way to do this is to drop the article column, make your change, then re-add the article column. You can do this via sp_repldropcolumn and sp_repladdcolumn. You can find more information about these two procs in Books Online. You can also drop the entire article, make your change, and re-add the article.

In SQL 2005, there's a lot of improvement in DDL so you can do ALTER TABLE directly on the article table.

|||

Greg Y wrote:

You can also drop the entire article, make your change, and re-add the article.

Note, that in some cases (for example, if you have anonymous pull subscriptions), dropping and re-adding entire article into publication may cause subscription become obsolete and requires reinitialization.