Update Query

Question

User-1068576637 posted

Table1
Column1	Column2
100A	1/9/2019
560B	10/1/2019
110AB
890CD

Table2
ColumnA	ColumnB
100A	110AB
560B	890CD

Using Sql server2008 , i Want to update Column2 in Table1, Based on Table 2 .

Whatever date is applicable for columnA value should take same datevalue for ColumnB also.

Example Result should be

Result of Table 1
Column1	Column2
100A	1/9/2019
560B	10/1/2019
110AB	1/9/2019
890CD	10/1/2019

Kindly give the update query 

Answer 1

User-2082239438 posted

Use below query.

UPDATE Table1 SET Table1.Column2=Table2.ColumnA
FROM Table1
INNER JOIN Table2 ON Table1.ColumnA=Table2.ColumnA

Answer 2

User-1068576637 posted

You have mistaken , i want to update date column in table 1 of respective row.

Answer 3

User-2082239438 posted

Check this.

CREATE TABLE TABLE1
(
	Column1 VARCHAR(20),Column2 VARCHAR(20)
)

INSERT INTO TABLE1
(
	Column1,Column2
)
VALUES('100A','1/9/2019')
INSERT INTO TABLE1
(
	Column1,Column2
)
VALUES('560B','10/1/2019')
INSERT INTO TABLE1
(
	Column1,Column2
)
VALUES('110AB','')	 
INSERT INTO TABLE1
(
	Column1,Column2
)
VALUES('890CD','')


CREATE TABLE Table2
(
	Column1 VARCHAR(20),Column2 VARCHAR(20)
)
INSERT INTO Table2
(
	Column1,Column2
)
VALUES('100A','110AB')	 
INSERT INTO Table2
(
	Column1,Column2
)
VALUES('560B','890CD')


--SELECT TABLE1.Column1,ISNULL((SELECT TOP 1 TABLE1.Column2 FROM TABLE1 WHERE TABLE1.Column1=A.Column1),TABLE1.Column2) AS Column2
--FROM TABLE1
--LEFT JOIN Table2 AS A ON TABLE1.Column1=A.Column2

UPDATE TABLE1 SET Column2=ISNULL((SELECT TOP 1 TABLE1.Column2 FROM TABLE1 WHERE TABLE1.Column1=A.Column1),TABLE1.Column2)
FROM TABLE1
LEFT JOIN Table2 AS A ON TABLE1.Column1=A.Column2

SELECT *
FROM TABLE1


-------------------OUTPUT
--Column1	Column2
--100A	1/9/2019
--560B	10/1/2019
--110AB	1/9/2019
--890CD	10/1/2019

DROP TABLE TABLE1
DROP TABLE TABLE2

Answer 4

User-1068576637 posted

Thanks  you very much

Answer 5

User77042963 posted

Merge TABLE1 tgt
using (
 Select t1.Column1,t.dt as Column2 from TABLE1 t1 join(
Select t2.Column1 ,t2.Column2,t1.Column2  dt from  TABLE1 t1   join    TABLE2 t2 on t1.Column1=t2.Column1
 ) t on  t1.Column1=t.Column1 or  t1.Column1=t.Column2) src on tgt.Column1=src.Column1
 WHEN MATCHED THEN
 UPDATE SET Column2=src.Column2;

 Select * from TABLE1