Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

T-Sql Update based on count

Table1:
ID      Value1
34      95
23      90

Table2:
ID      Value1  Value2
34        91          89
23        88          86

Table3:
ID       Role
34       P
34       J
23      P

Need an UPDATE statement that counts the ID's in Table3 and if the count is more than 1.  It should update Table1 with Value1 from Table2.  If the count is only one then it should UPDATE Table1 with Value2.

Update table1
set table1.Value1 = ?
0
moni81011
Asked:
moni81011
2 Solutions
 
Saurabh BhadauriaCommented:
Update
set table1.Value1 =  case when (select count(*) from table3 as t3 where t3.id=t1.id  ) = 1   then t2.value2
 when  (select count(*) from table3 as t3 where t3.id=t1.id  ) > 1 then t2.value2
end
from table1 t1  join table2 t2 on t1.id=t2.id
0
 
lwadwellCommented:
Try
UPDATE table1
SET Value1 = CASE WHEN v.cnt = 1 THEN table2.Value2 ELSE table2.value1 END
FROM table1
INNER JOIN table2 ON table1.ID = table2.ID
INNER JOIN (SELECT id, count(*) cnt FROM table3 GROUP BY id) v ON table1.ID = v.ID

Open in new window

0
 
QlemoC++ DeveloperCommented:
The latter approach is to prefer, as it will perform much better, and is more straight-forward.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now