Adding details to multiple records

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 details to multiple records

Author

Topic

bmfjon
Starting Member

14 Posts

Posted - 2007-11-29 : 04:54:09

Hello

I am trying to add a website address to all the records in a database that match a certain criteria. The statement that I am using is shown below, but surprise surprise, it's not working! I'm new to SQL so any help would me much appreciated! Thanks.

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

insert into "communications"
(contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number)
values (NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum);
select name
from organisations
where name ='Abc Limited'

So, I have not included a VALUE for CONTACT_NUMBER as I wish to update all records with the website details as per the INSERT statement where the NAME column in the ORGANISATIONS table is 'Abc Limited'. I know something is wrong but I can't quite work out what!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-29 : 04:59:24

Do you mean something like this?

insert into [communications]
(contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number)
select contact_number, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum
from organisations 
where name ='Abc Limited'

Note: Assuming you have CONTACT_NUMBER column in ORGANISATIONS table

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

bmfjon
Starting Member

14 Posts

Posted - 2007-11-29 : 05:04:15

Hmmm...

I get the following error when I try that:-

Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value 'Abc Limited' to a column of data type int.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-29 : 05:05:32

That's because it looks like you put the company id in there, not the name. Try changing it to your id (dunno which field as you haven't said).

bmfjon
Starting Member

14 Posts

Posted - 2007-11-29 : 05:12:05

Sorry, I don't understand!

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

insert into "communications"
(contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number)
select name, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum
from organisations
where name ='Abc Limited'

The table organisations has a column in it called name which holds lots of different companies names some of which are e.g. Abc Limited. I would like to update the communcations table of all companies whose name in the organisations table is 'Abc Limited' with the website address.

I don't understand what you mean about the id.

I'm really sorry if I'm being thick here. Thanks for all your help!

Cheers

Jon

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-29 : 05:19:46

look at your insert statement. the first column you've specified is contact_number, but the first column you've selected for the insert is name.

Em

bmfjon
Starting Member

14 Posts

Posted - 2007-11-29 : 05:21:12

Hello

Sorry, I have just spotted the additional edit that you made to your post harsh_athalye... thank you.

I can confirm that contact_number is present in the ORGANISATIONS table but I now get the following error when I run the statement:-

Server: Msg 2601, Level 14, State 3, Line 4
Cannot insert duplicate key row in object 'communications' with unique index 'c2962cn7081'.
The statement has been terminated.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-29 : 05:24:36

Which column in Communications table has unique index on it?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-29 : 05:25:30

What is your primary key on communications?

You can find this in SQL Management Studio, if you expand the table, you will see keys, if you double click the gold key, this will tell you what field(s) your primary key is on.

bmfjon
Starting Member

14 Posts

Posted - 2007-11-29 : 05:29:29

Hi Both

It's contact_number

Thanks

Jon

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-29 : 05:34:02

Try this:

insert into [communications]
(contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number)
select o.contact_number, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum
from organisations o Left Join Communications c
on o.contact_number = c.contact_number
where o.name ='Abc Limited' and c.contact_number IS NULL

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

bmfjon
Starting Member

14 Posts

Posted - 2007-11-29 : 05:36:13

Thanks, it worked!!!

Cheers

Jon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources