Update Query with Case inner join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update Query with Case inner join

Author

Topic

kashif.special2005
Starting Member

10 Posts

Posted - 2012-06-19 : 08:01:13

Hi,

I want to update a Table1.class column with Table2 tables data with inner join with condition that
When age<=10 then 2 else 2, and I am using below query but it is giving me an error message that

update Table1
set class=
case
t.age <= 10 then 2
else
3
end
from Table1 v inner join Table2 t on v.id=t.id

Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '<'.

Table 1
id subject class
100 B NULL
100 E NULL
100 R NULL
101 R NULL
101 K NULL
101 A NULL
102 Z NULL
102 L NULL
102 N NULL
102 O NULL

Table 2
id age
100 10
101 15
102 8

Please help to resolve this problem.

Thanks
Kashif

matty
Posting Yak Master

161 Posts

Posted - 2012-06-19 : 08:05:04

[code]
update v
set class= case WHEN t.age <= 10 then 2
else 3
end
from Table1 v
inner join Table2 t
on v.id=t.id
[/code]