0

I have added to my existing table1 new column (col7). Structure is as below on picture. Here is the SQL statement which produces the table:

select year, month, quater, col4, col5, col6, col7 from table1

enter image description here

Based on table2, I want to update values into col7 in table1 (insert all values from table2 to table1).

My unique key is: year-month-quater-col4-col5-col6

Here is structure of table2: enter image description here

I didn't do before this update. Can you kindly please help how to do it? Thank you!

4est
  • 3,010
  • 6
  • 41
  • 63
  • Possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) – neer Jul 14 '16 at 08:43

2 Answers2

1

You can use below query

UPDATE T1 
SET T1.Col7 = T2.Col7
FROM table1 T1 
inner join table2 T2 on T1.Year = T2.Year AND T1.month = T2.month AND T1.quarter = T2.quarter AND T1.col4 = T2.col4 AND T1.col5= T2.col5 AND T1.col6 = T2.col6

Refer below link SQL Server - inner join when updating

Community
  • 1
  • 1
arpan desai
  • 889
  • 2
  • 13
  • 23
1

If you have record in table2 but missing in table1 then you can insert into table1 by following code, Apart for the update code which is provided by @arpan desai, you need to use the below code for inserting your missing record

INSERT INTO TABLE1 SELECT * FROM 
(SELECT  T2.year,T2.month,T2.quarter,T2.COL4,T2.COL5,T2.COL6,T2.COL7 FROM TABLE2 T2
EXCEPT
SELECT  T1.year,T1.month,T1.quarter,T1.COL4,T1.COL5,T1.COL6,T1.COL7 FROM TABLE1 T1) M
PowerStar
  • 893
  • 5
  • 15