Update Query Needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Query Needed

Author

Topic

Leo_Don
Starting Member

42 Posts

Posted - 2008-11-03 : 07:24:39

Hi All,

I have the following table

ID Name
-----------------
A0245 abc
A1234 efg
B2454 pqr
B5647 xyz
A9999 lmn
B8888 asd

I want to update the Column ID by removing the first character and incrementing it by 1.
i.e i want to replace A0245 with 0246 = 0245 + 1
And i have to rows A9999 and B8888 which should not be changed

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 07:31:24

UPDATE Table
SET ID=LEFT(ID,1)+RIGHT('0000'+ CAST(RIGHT(ID,4)+1 AS varchar(4)),4)
WHERE RIGHT(ID,4)<> '9999'
OR RIGHT(ID,4)<> '8888'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-03 : 07:37:20

I think OP wanted to remove first char.
And the OR schould be an AND, shouldn't it?

UPDATE Table
SET ID=RIGHT('0000'+ CAST(RIGHT(ID,4)+1 AS varchar(4)),4)
WHERE RIGHT(ID,4)<> '9999'
AND RIGHT(ID,4)<> '8888'

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Leo_Don
Starting Member

42 Posts

Posted - 2008-11-03 : 07:42:37

can i give

UPDATE Table
SET ID=LEFT(ID,1)+RIGHT('0000'+ CAST(RIGHT(ID,4)+1 AS varchar(4)),4)
WHERE RIGHT(ID,5)<> 'A9999'
OR RIGHT(ID,5)<> 'B8888'

I want these two ID to be unchanged A9999 and B8888.
They both should be the same

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-03 : 07:58:32

[code]create table #test(id varchar(5), Name varchar(255))
insert #test
select 'A0245', 'abc'
union select 'A1234', 'efg'
union select 'B2454', 'pqr'
union select 'B5647', 'xyz'
union select 'A9999', 'lmn'
union select 'B8888', 'asd'

select * from #test

UPDATE #test
SET ID=RIGHT('0000'+ CAST(RIGHT(ID,4)+1 AS varchar(4)),4)
WHERE id <> 'A9999'
AND ID<> 'B8888'

select * from #test

drop table #test
[/code]

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Leo_Don
Starting Member

42 Posts

Posted - 2008-11-04 : 03:45:17

quote:
Originally posted by Leo_Don

Hi All,

I have the following table

ID Name
-----------------
A0245 abc
A1234 efg
B2454 pqr
B5647 xyz
A9999 lmn
B8888 asd

I want to update the Column ID by removing the first character and incrementing it by 1.
i.e i want to replace A0245 with 0246 = 0245 + 1
And i have to rows A9999 and B8888 which should not be changed

Thanks in advance

Need a small Change in the Query....
I want a Query for the above table to take the max value of A and increment it by 1 and display but not selecting A9999 and then in the same way for B as well but not change the value of B8888

i.e Of the ID colum A1234 and B5647 is the Max value, The final result shold be

ID
-----
A1235
A9999
B5648
B8888

Here A1235 = A1234 + 1
B5648 = B5647 + 1

and the Vales A9999 and B8888 are not considerd...
Hope everthing is clear...

Thanking you once again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 03:50:05

And you want set this max value to all other records of same series?

Leo_Don
Starting Member

42 Posts

Posted - 2008-11-04 : 03:55:19

I have Huge no or records in the ID Column which Statrts with A or B
and want the MAX of A +1 abd B +1 but do not consider A9999 abd B8888 apart from that i want the max of A and B

Leo_Don
Starting Member

42 Posts

Posted - 2008-11-04 : 05:22:11

I could find the max value In the colum ID can anyone help me incrementing the max value by 1
below is the Query to find the max value

select max(id) as id from #test where ID <> 'A9999' and id<>'B8888' group by left(id,1)

Leo_Don
Starting Member

42 Posts

Posted - 2008-11-04 : 06:07:51

quote:
Originally posted by Leo_Don

I could find the max value In the colum ID can anyone help me incrementing the max value by 1
below is the Query to find the max value

select max(id) as id from #test where ID <> 'A9999' and id<>'B8888' group by left(id,1)

CAn any one help me here

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-04 : 06:35:13

Assuming the updates we have discussed before are NOT EXECUTED because in this case there are A and B in the table anymore...
Assuming now you need only this described select-statement.

select LEFT(max(id),1)+CAST(RIGHT(max(id),4)+1 AS varchar(5)) as id
from #test
where ID <> 'A9999' and id<>'B8888' group by left(id,1)

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Leo_Don
Starting Member

42 Posts

Posted - 2008-11-04 : 06:50:21

Thanx Guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources