I have two tables, one something like this:
Table 1:
ID- Name- Code- Code 2-
1- John- 115-null
2- Rick- 652-null
3- Jones- 886-null
4- James- 554-null
5- Elton- 125-null
6- Craig- 214-null
7- John- 452-null
Table 2:
Name- Code- Code 2-
John- 115- a
Rick- 652- b
Jones- 886- c
James- 554- d
Elton- 125- e
Craig- 214- f
John- 452- g
Craig- 886- h
Rick- 115- i
This isn't the real data, it's not quite that simple. I need to get Code 2 from Table 2 into the Code # column in Table 1. To do this, I need to match up BOTH the Name and Code columns to get the data from Column 'Code 2' into Column 'Code #'. It needs to match against at least two columns as there are duplicates in each...
I want to end up with something like:
ID- Name- Code- Code 2-
1- John- 115-a
2- Rick- 652-b
3- Jones- 886-c
4- James- 554-d
5- Elton- 125-e
6- Craig- 214-f
7- John- 452-g
You can join tables on multiple columns at once like:
select t1.id, t1.name, t1.code, t2.code2
from t1
inner join t2 on t1.name = t2.name
and t1.code = t2.code
this way (from your example) "John 115" will only be matched with "John 115" rather than "John 452", as the join is only being performed where both the name and code between the two tables are equal. (Note John 452 will also join to John 452).
You can build update statements based on selects if you were unaware. Your update statement would end up looking something like this:
update t1
inner join t2 on t1.name = t2.name and t1.code = t2.code
set t1.code2 = t2.code2;
This will join the two tables where ever name and code match, and will set code2 in the first table equal to code2 from the second table.
Here is an SQL Fiddle example.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments