Adding a value + 1 to a record

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

Adding a value + 1 to a record

Author

Topic

bmfjon
Starting Member

14 Posts

Posted - 2007-11-29 : 04:33:47

Hello

I wish to add some records to my database but I am having problems asking the SQL commands to add 1 to the previous number.

I'll explain what I mean....!

The database automatically increases the communication_number once each time a record is added. However I'd like to amend the command below to say "add 1 to the current communcation_number" rather than input a number manually as I have done below:-

insert into "communications"
(address_number, contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number)
values (105, NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, '30001');

Any help would be much appreciated.

Thanks,

Jon

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-29 : 04:40:00

IF the database already adds one to it, you shouldn't need to worry about it as you should have it set up as an identity and it does that automatically.

Otherwise, the following will work:


declare @ComNum int
set @ComNum = (select max(communication_number)+1 from communications)

insert into "communications"
(address_number, contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number)
values (105, NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum);

bmfjon
Starting Member

14 Posts

Posted - 2007-11-29 : 04:44:39

Thanks for your help Rick, that worked great!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-29 : 04:50:47

Just a caveat for this, If you have many users, then this may cause problems if there are a lot of inserts into the table as you may get the same number inserted if two people insert at the same time.

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-29 : 05:59:41

hi, i think in this kind of problem this will make it more user friendly
like make a trigger that will update the table itself after a succesfull insertion is done
to the table.
user just have to insert 0 as a dummy value and trigger will update it self. it will take a little
bit more time which will be overcome by that user will have to just run a query to insert the data
and 0 in corresponding position

create table testing(id int,[name] char(12))

create trigger trg_testing on testing for insert
as
begin
declare @var integer
select @var=coalesce(max(id),0)+1 from testing
update testing set id=@var where id=0
end
go

insert into testing values(0,'asd')
insert into testing values(0,'aaa')
select * from testing
output

1 asd
2 aaa

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-29 : 06:23:51

Never use a trigger unless you really have no other choice.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-29 : 06:33:05

For avoid concurrency problem, make number generation and record insertion as single atomic operation like this:

insert into "communications"
(address_number, contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number)
Select 105, NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, ComNum
From (Select max(communication_number)+1 as ComNum from communications) temp

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-29 : 16:27:16

quote:
Originally posted by arorarahul.0688

hi, i think in this kind of problem this will make it more user friendly
like make a trigger that will update the table itself after a succesfull insertion is done
to the table.

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

Wrong answer yet again, Rahul.

As RickD said, never use a trigger unless you absolutely have to.

In this case, an identity column would be the most "user friendly" solution; the SELECT MAX(col) + 1 is the second, but not as safe (as RickD said also).

Please don't answer questions until you've

1. Read and understood the entire question, including the solution the poster is looking for.

2. Read each and every previous response to the question, so you can see other answers already provided and possibly increase your understanding of the problem.

3. At least have an idea of what you're talking about when you post a response.

This would avoid the majority of the wrong answers you've posted here, and waste a lot less time of the people who have to correct your wrong answers.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-11-30 : 00:00:44

As RickD said, Identity is the only way to do this efficiently, accurately and easily.

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-30 : 01:30:11

quote:
Originally posted by KenW

quote:
Originally posted by arorarahul.0688

hi, i think in this kind of problem this will make it more user friendly
like make a trigger that will update the table itself after a succesfull insertion is done
to the table.

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

Wrong answer yet again, Rahul.

quote:

As RickD said, never use a trigger unless you absolutely have to.

In this case, an identity column would be the most "user friendly" solution; the SELECT MAX(col) + 1 is the second, but not as safe (as RickD said also).

MAKE your own opinion KENW i knew what RICK has told me

.

you understand the problem well plz tell which column in the present structure will be the identity key

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-30 : 15:29:46

communcation_number, why do you ask? The OP specifically said what field they are trying to increment automatically.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources