SQL code to merge tables that do not match exactly

Question

I have a table that looks like this:

CODE ANIMAL

123  "Alligator"
1254 "Cardinal"
5675 "Golden Retreiver"

And I have another table that looks like this:

CODE  TYPE
12367 Reptile
12    Bird
56    Dog

I want to add the column Type to the first table based on the code. I am not sure how to proceed.

Why are you wanting to do that? Since you have a relational database, simply put the "code" from the second table into the first table. — Nick, May 28 '12 at 20:38
Manually I'm afraid. You should have added the type of animal to the animal table as you went. — Ben, May 28 '12 at 20:43
The first two line seem backwards, if the first lines code number where reversed then you could add based on a LIKE statement where T1.CODE LIKE (T2.CODE + '%'). because 12367 would be like 123. — Bit, May 28 '12 at 20:54

Tony Hopkinson · Accepted Answer · 2012-05-29T16:59:09.250

Well first job is to add the column with an alter table statement.

As in something like

Alter Table Animals Add AnimalTypeCode int null

Then you need to poulate it, that would be with a bunch of update statements

Say with something like

Update Animals 
Set AnimalTypeCode = 12367
Where Code = 123

or if say Crocodile was 679

Update Animals 
Set AnimalTypeCode = 12367
Where Code in (123,679)

Or if you prefer it a bit more readable

Update Animals 
Set AnimalTypeCode = 12367
Where Animal in ('Alligator','Crocodile')

Keep bashing away until they all got one, perhaps adding some new types as required, thenn you can add a foreign key.

No easy way to do this unless you've stashed away a table somewhere linking animal to animal type

Editted to add. Given AnimalCode = AnimalTypeCode * 100 + ? then

Update Animals
Set AnimalTypeCode = Cast(AnimalCode / 100 as Int)

Might do the job

Select * 
From Animals
outer join AnimalTypes On Animals.AnimalTypeCode = AnimalTypes.Code
Where AnimalTypes.Code is null

Will give you all those where animaltypecode is not pointing to an existing animal type.

The one you'll have to be wary of is 1269, Elephant, Bird(12). If you have anything like that you'll have to fix them individually.

The codes are supposed to be similar. I have 1000 rows in first table and 100 rows in second table. — cool_cs, May 28 '12 at 21:27

1 Answers1