1

I have 2 tables:

id | bsi | cell 

1  | 45 | NULL
2  | 23 | 600-1167
3  | 47 | 
4  | 89 | 501- -
5  | 98 | 603-5670

I would like to replace cell values with data from another colum in another table matching both tables by bsi

Here is my 2nd table:

id | bsi | contact 

1  | 45 | 610-5896 
2  | 23 | 605-4567 
3  | 47 | NULL 
4  | 89 | 689-9089
5  | 98 | NULL

I would like each value in the first table replaced with the new value in 2nd table also the value where both tables match in bsi code ie to replace the NULL and '501- -' with the new value once there is one in the 2nd table.

So result should be an updated table as follows:

id | bsi | cell 

1  | 45 | 610-5896
2  | 23 | 605-4567
3  | 47 | NULL
4  | 89 | 689-9089
5  | 98 | 603-5670

I have tried this query but it only replaces the new values and seems to write null values for items that are in table1 and not table2. I want to keep the table1 values that are in table 1 but not table 2. 

UPDATE Table1 
   SET Table1.cell = Table2.Contact
   FROM Table1  INNER JOIN Table1 ON Table1.bsi = Table2.bsi
Improve this question

2 Answers 2

Reset to default
1

Your join seems to be a little off... you aren't joining the second table

UPDATE t1
   SET t1.cell = t2.Contact
   FROM Table1  t1
   INNER JOIN Table2 t2 ON           --changed to table2
   t1.bsi = t2.bsi 
   --where <some condition>

Also, it can be faster to just update all rows than to be selective using a where clause. See Aaron Bertrand's tests on this. I did it this way because despite you stating that I want to keep the table1 values that are in table 1 but not table 2, this wasn't reflected in your expected output. If you really do want to conditionally update them, then you need to add the where clause.

SEE DEMO HERE

create table table1 (id int, bsi int, cell varchar(16))

create table table2 (id int, bsi int, contact varchar(16))

insert into table1 values
(1,45,NULL),
(2,23,'600-1167'),
(3,47,''), 
(4,89,'501- -')

insert into table2 values
(1,45,'610-5896'), 
(2,23,'605-4567'), 
(3,47,NULL), 
(4,89,'689-9089')

UPDATE t1
   SET t1.cell = t2.Contact
   FROM Table1  t1
   INNER JOIN Table2 t2 ON           --changed to table2
   t1.bsi = t2.bsi 
   --where <some condition>


select * from table1

RETURNS

+----+-----+----------+
| id | bsi |   cell   |
+----+-----+----------+
|  1 |  45 | 610-5896 |
|  2 |  23 | 605-4567 |
|  3 |  47 |          |
|  4 |  89 | 689-908  |
+----+-----+----------+
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

I tried this and it replaces my values in table 1 with NULL
If I add where t1.cell <> t2.Contact it only replaces the values where there isnt a match
Here is a demo dbfiddle.uk/… with your exact data
@scsimon There is something missing there. It could be the sample data or the OP want something else and can't explain well.
I don't see how @Sami my code produces the expected results in the OP with the sample data supplied. But who knows lol
|
0

It looks like your query was joining back on itself. Change the table you are joining on to be Table2 instead.

This will update only rows in Table1 that match up with rows in Table2 by the bsi value, setting the cell value to Contact

UPDATE Table1 SET Table1.cell = Table2.Contact FROM Table1
INNER JOIN Table2 ON Table1.bsi = Table2.bsi

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.