Update - tabel

johnkainn
johnkainn used Ask the Experts™
on
TableA (AId (int), AText (varchar(50)), BMoreThen1(bit))
TableB (BId(int), BText(varhar(50)), AId(int))
If there are more then 1 row in TableB with same AId, I would like to set BMoreThen1 column in TableA to true, else false.
How is best to do that?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
update A
     set BMoreThen1 = case when ( select count(*) from tableB b where b.Aid = a.Aid ) > 1 then 1 else 0 end
  from tableA a
Maybe not the "best" way but this will work (updates the whole table):
 
UPDATE TableA
SET BMoreThen1 = (SELECT CASE WHEN COUNT(TableB.AId) > 1 THEN 1 ELSE 0 END FROM TableB WHERE TableA.AId = TableB.AId)

Open in new window

Commented:
Update (TableA x Inner Join
(Select (Case When C1 = 1 Then False Else True End) as C2, AId From (
select count(*) as C1, AId from TableB group by AId)) y
On x.AId = y.AId) set BMoreThen1 = C2

The above should work ... I have not tried it (I don't have access to a SQL server right now).

Essentially, the steps are as follows:

1.  select count(*) as C1, AId from TableB group by AId
2.  Select (Case When C1 = 1 Then False Else True End) as C2, AId From (
select count(*) as C1, AId from TableB group by AId)
3.  Combine the query from step 2 with the Table A and run the update.

This is assuming that there is at least one AId in TableB.  If that is not the case, then you'll need to do a Left join (instead of the inner join), and use the ifnull(C2, False).

Ss
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
Commented:
update tableB set morethanone = (Case When (Select Count(AID) from TableA where TableA.AID = AId) > 1 then 'true' else 'false' end)  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial