2

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
apomene
  • 14,282
  • 9
  • 46
  • 72
Tfom
  • 89
  • 1
  • 11

1 Answers1

5

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.

AdamMc331
  • 16,492
  • 10
  • 71
  • 133
Kritner
  • 13,557
  • 10
  • 46
  • 72
  • Hi Kritner, Would that update the NULL values in table 1 to the Code 2 values in Table 2 ONLY? That's the only column I wish to pull through.. – Tfom Jul 24 '15 at 13:14
  • The above won't update *anything* it is simply a select statement. – Kritner Jul 24 '15 at 13:16
  • The update statement should be simple enough to build based on the select statement though... see http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query – Kritner Jul 24 '15 at 13:18
  • @Kritner the syntax is slightly off for the update. Can I have your permission to edit? – AdamMc331 Jul 24 '15 at 13:29
  • @Thomh and Kritner, I fixed the update syntax and I added a Fiddle too (because I was testing it, and there's no point giving a second answer). If you want this to only update where the value is `NULL` in table one, you can just add that to your join condition: `ON t1.name = t2.name AND t1.code = t2.code AND t1.code2 IS NULL`. See [this Fiddle link](http://sqlfiddle.com/#!9/b1330/1) for a row that doesn't get updated because it wasn't null. – AdamMc331 Jul 24 '15 at 13:39