CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
DooDoo
DooDoo

Reputation: 13447

How to update a table according records in another table

Consider this two tables :

tbl 1

Qsno      City  Status  Year    Month
--------------------------------------
1          01     3      1990     1
2          01     3      1990     1
1          02     3      1990     2
2          02     3      1990     2
1          03     3      1990     1
2          03     1      1990     1
3          03     1      1990     1

and :

tbl 2 

Qsno      City  
---------------
 1          01   
 2          01   
 1          03   
 2          03   
 3          03

Qsno + City are unique

Ok, I want to update row from tbl1 that has row in tbl2 and set Month = 3.

How I can do this?

thanks

Upvotes: 1

Views: 219

Answers (2)

Igor Borisenko
Igor Borisenko

Reputation: 3866

Also you can use this

use tempdb
go

create table #tbl1 (Qsno int,City int, intMonth int)
create table #tbl2 (Qsno int,City int)

insert into #tbl1 values (1,2,1),(1,3,1),(2,2,1),(2,3,1),(3,1,1)
insert into #tbl2 values (1,2),(2,2)

UPDATE t1
SET intMonth=3
FROM #tbl1 t1
     JOIN #tbl2 t2 ON t1.Qsno=t2.Qsno AND t1.City=t2.City

SELECT * FROM #tbl1

DROP TABLE #tbl1
DROP TABLE #tbl2

And don't use reserved words such as Month, Year, Status etc as names of Tables and Columns thus you will avoid a lot of headaches.

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

update tbl1
set Month = 3
where exists
  (select *
   from tbl2
   where tbl1.Qsno = tbl2.Qsno and
         tbl1.City = tbl2.City)

Upvotes: 4

Related Questions