Merging 2 fields from diff tables into 1 field

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

Merging 2 fields from diff tables into 1 field

Author

Topic

Jollesax
Starting Member

4 Posts

Posted - 2009-08-06 : 14:38:49

Hi,

I know this is probably answered already a thousand times, but I don't get it working.

I have two tables, one with DNA sequences and another with species. Each species has a code, and each sequence has a VIMSSID. I need in the table of sequences a new field, OrthID, that is the concatenation of the code and the VIMSSID.

Each species has an NCBIID that is referred to in the sequence table

eg : Species table with record : code =PA NCBIID = 450
sequence table with records
VIMSSID NCBIID
112 450
113 450
... ...

Then I need a sequence table like this :
VIMSSID NCBIID OrthID
112 450 PA112
113 450 PA113
...

I tried numerous things, the last one was the following code :

SELECT [species.Code]&""&[sequence.VIMSSID] AS sequence.OrthID
FROM species INNER JOIN sequence ON species.NCBIID=sequence.NCBIID

Nothing works. Help is appreciated.

Kind regards

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-06 : 14:53:02

SELECT species.Code + sequence.VIMSSID AS OrthID
FROM...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-06 : 14:54:41

the above assumes they are both character types. if they are integer types, then need to cast them

SELECT Convert(varchar(32), species.Code) + Convert(varchar(32), sequence.VIMSSID) AS OrthID

Jollesax
Starting Member

4 Posts

Posted - 2009-08-06 : 16:07:27

quote:
Originally posted by russell

the above assumes they are both character types. if they are integer types, then need to cast them

SELECT Convert(varchar(32), species.Code) + Convert(varchar(32), sequence.VIMSSID) AS OrthID

Tried both solutions, but it keeps giving me that dreadful #1064 error. I'm baffled a bit, don't know what's wrong. I use phpMyAdmin to put my SQL commands, could that make things different?

When I try the command just with Select species.Code,sequence.VIMSSID FROM... , it works. I get the two columns. But I can't seem to merge them and I definitely don't get them into that field OrthID

Jollesax
Starting Member

4 Posts

Posted - 2009-08-06 : 16:14:43

AH! I found a function concat() for MySQL (sorry, I didn't realize I was on SQL server fora, and that it's differrent than MySQL. Newbies, you see...)

Now I just need to find a way of saving that outcome into that field. I tried now :
SELECT CONCAT( species.Code, sequence.VIMSSID ) AS 'sequence.OrthID'
FROM species
INNER JOIN sequence ON species.NCBIID = sequence.NCBIID

And I get a result displayed, but it's not saved in that field, alas.

Jollesax
Starting Member

4 Posts

Posted - 2009-08-06 : 17:14:44

I took the ugly way out. It hideous code, far from efficient, but it works, it's MySQL (still don't understand the diff with SQL Server too much, but well...), and it might help somebody someday :

CREATE TABLE 'database'.'temp' (
'OrthID' VARCHAR( 32 ) NOT NULL ,
'VIMSSID' VARCHAR( 32 ) NOT NULL ,
PRIMARY KEY ('VIMSSID' ) ,
UNIQUE ('OrthID')
);

INSERT INTO temp( OrthID, VIMSSID )
SELECT CONCAT( species.Code, sequence.VIMSSID ) AS 'OrthID', sequence.VIMSSID
FROM species
INNER JOIN sequence ON species.NCBIID = sequence.NCBIID;

UPDATE sequence,temp
SET sequence.OrthID=temp.OrthID
WHERE sequence.VIMSSID=temp.VIMSSID;

DROP TABLE temp;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources