1

Table1: tb1

|ID|Reference|
|1|ABCD|
|2|EFGH|
|3|IJKL|
|4|MNOP|
|5|MNOP|

Table2: tb2

|Reference  |UpdatedID  |
|ABCD       |           |
|ABCD       |           |
|EFGH       |           |
|EFGH       |           |
|EFGH       |           |
|EFGH       |           |
|IJKL       |           |
|MNOP       |           |
|MNOP       |           |

I need to update an empty column of Table tb2 on the basis of tb1.

The common column to join is the Reference column of both tables where ID of tb1 will be updated to the UpdatedID column of the tb1 table.

After applying the join column the record is getting increased as in tb1 table the same Reference has a different ID which needs to be updated same in tb2 table.

How I am supposed to proceed.

JOIN query used:

select 
    A.csvRefNumber,
    A.reference,
    B.reference
from 
    tb1 A
left join 
    tb2 B on A.Reference = B.Reference
order by 
    csvRefNumber

Required output:

|Reference  |UpdatedID  |
|ABCD       |1          |
|ABCD       |1          |
|EFGH       |2          |
|EFGH       |2          |
|EFGH       |2          |
|EFGH       |2          |
|IJKL       |3          |
|MNOP       |4          |
|MNOP       |5          |
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
shishir
  • 851
  • 3
  • 11
  • 27
  • 1
    You need to elaborate here. It is hard to figure out, what exactly you want. Can you please try to rephrase your question? Do you want to join the two tables to see what similarities there are between them or? – Alexander Falk Jul 31 '18 at 12:39
  • ? Why do two values of MNOP get different UpdatedID values? What is the point of storing this number? You can always just generate that value at query time using `RANK() or DENSE_RANK() over (PARTITION BY Reference)`. – Aaron Bertrand Jul 31 '18 at 12:45
  • +Aaron This is just a sample data from my big dataset, where there are multiple records available like this. – shishir Jul 31 '18 at 12:47
  • What would be the updatedId if another MNOP is there in tb2? – Ajay Gupta Jul 31 '18 at 14:02
  • +Ajay Gupta If the MNOP got the ID 6 in tb1 table then 6 should get updated in the UpdatedID column of tb2 table. – shishir Aug 01 '18 at 05:40

2 Answers2

0

You can do this:

UPDATE b SET b.UpdatedID = a.ID
FROM 
tb1 A
LEFT JOIN tb2 B
ON A.Reference = B.Reference

It's basically joining the tables and updating. I usually start (like you did) with a SELECT statement, then change it to UPDATE. Then then you specify the table you want to update by it's alias and lastly which column (or columns) you want to set equal.

sniperd
  • 5,124
  • 6
  • 28
  • 44
  • I have just updated the required output. The all logic mixed up with the Reference = MNOP – shishir Jul 31 '18 at 12:45
  • You need a way to `JOIN` so your `SELECT` is aligned the way you'd like it. So you'd have to have another kind of ID column. – sniperd Jul 31 '18 at 12:49
0

I think this is what you want:

update tb2
    set updateid = tb1.id
    from tb2 join
         tb1
         on tb2.reference = tb1.reference;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • If I use the above query it will update the reference 'MNOP' with ID = 4, where I need to update with its recurring ID in the tb1 table. – shishir Jul 31 '18 at 12:46