adding data to existing data - 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

SQL Server 2000 Forums

Transact-SQL (2000)

adding data to existing data

Author

Topic

TerryNL
Starting Member

21 Posts

Posted - 2008-03-13 : 07:34:02

Hi
i have a question about inserting data..
i have a column like this

dna
-----
151C>T
768 G>T
2588G>C
delEX+1-15+

now, i must insert into every existing field "c."
so the new column must be:

dna
-----
c.151C>T
c.768 G>T
c.2588G>C
c.delEX+1-15+

is there an easy way to do that?

thanks

greetz, TerryNL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-13 : 07:38:49

[code]update t
set dna = 'c.' + dna
from yourtable t
[/code]

or you can just prefix it during select
[code]
select 'c.' + dna
from yourtable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-13 : 08:22:10

Also make sure the column has enough size to hold data

Madhivanan

Failing to plan is Planning to fail

TerryNL
Starting Member

21 Posts

Posted - 2008-03-13 : 08:24:58

great, thanks
but in Microsoft Access 2007 the system gives an error on the same database
syntaxerror (operator is missing) in query-expression
update d
set dna = 'c.' + dna
from dna1 d

any idea how i must solve this?
this would really help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-13 : 08:35:00

Try

update dna1
set dna = 'c.' + dna

Madhivanan

Failing to plan is Planning to fail

TerryNL
Starting Member

21 Posts

Posted - 2008-03-13 : 08:52:40

great many thanks
it works exactly how is should work

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-13 : 09:01:07

quote:
Originally posted by TerryNL

great many thanks
it works exactly how is should work

You are welcome

Madhivanan

Failing to plan is Planning to fail

TerryNL
Starting Member

21 Posts

Posted - 2008-03-13 : 09:16:41

now i have a following question:

in the database i have this:

dna
--------
806_810delCCCTG
800 kb del
800 kb del
8 kb del
8 kb del
8 kb del
8 kb del
8 kb del
799C>T

the only things i want to update are 806_810delCCCTG
and 799C>T
but i want to update more from 806 up.. so 808C>T also needs to be updated
is there some code that can update records if there 801* and up??
with the star i mean that everthing can stand behind the first 3 numbers, so 801_hshe of 801_2818 should make no difference

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-13 : 09:25:01

quote:
Originally posted by TerryNL

great, thanks
but in Microsoft Access 2007 the system gives an error on the same database
syntaxerror (operator is missing) in query-expression
update d
set dna = 'c.' + dna
from dna1 d

any idea how i must solve this?
this would really help!

You have posted in SQL Server 2000 forum. The syntax i used does not work for Access. You should post in the Access forum for MS Access related question

KH
[spoiler]Time is always against us[/spoiler]

TerryNL
Starting Member

21 Posts

Posted - 2008-03-13 : 09:29:30

quote:
Originally posted by khtan

quote:
Originally posted by TerryNL

great, thanks
but in Microsoft Access 2007 the system gives an error on the same database
syntaxerror (operator is missing) in query-expression
update d
set dna = 'c.' + dna
from dna1 d

any idea how i must solve this?
this would really help!

You have posted in SQL Server 2000 forum. The syntax i used does not work for Access. You should post in the Access forum for MS Access related question

KH
[spoiler]Time is always against us[/spoiler]

I know that, but because you guys helped me so fast i thought i post it here
i've been helped much withyour answer and from the other answers

normally sql in sql server is pretty much the same as in access
so maybe someone has the answer to my newest post

TerryNL
Starting Member

21 Posts

Posted - 2008-03-14 : 03:07:15

quote:
Originally posted by TerryNL

now i have a following question:

in the database i have this:

dna
--------
806_810delCCCTG
800 kb del
800 kb del
8 kb del
8 kb del
8 kb del
8 kb del
8 kb del
799C>T

the only things i want to update are 806_810delCCCTG
and 799C>T
but i want to update more from 806 up.. so 808C>T also needs to be updated
is there some code that can update records if there 801* and up??
with the star i mean that everthing can stand behind the first 3 numbers, so 801_hshe of 801_2818 should make no difference

Nobody has an answer to my question?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-14 : 06:02:42

try

where left(col,3) between 801 and 806

Madhivanan

Failing to plan is Planning to fail

TerryNL
Starting Member

21 Posts

Posted - 2008-03-14 : 07:13:57

it works properly now!
thank you very much
saves me a lot of time, instead of changing 6000 records by hand i now can use a query :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources