CODEWITHSUNDEEP
sql-server-2008count
Bhupinder Singh
Bhupinder Singh

Reputation: 1071

SQL Server 2008 : Updating a column based on other tables

TableA (id int, match char(15), multiple char(10))

int match multiple
1   100  
2   101
3   102
4   103

TableB (match char(15), match2 char(10))

match  match2
100    ABC
100    NBG
101    NYF
102    NHW
102    UYQ
103    WOT

Now, I want to populate TableA.multiple = "YES" if in TableB for corresponding match, there exists more than one match2.

Expected result.

int match multiple
1   100    YES
2   101    NULL
3   102    YES
4   103    NULL

Thanks in advance !

My FAILED try:

Update A 
SET multiple = 'YES' 
From tableA A
Inner join tableB B ON A.match = B.match
WHERE (Select count(distinct(B.match2)) from TableB) > 2

Upvotes: 0

Views: 232

Answers (3)

egrunin
egrunin

Reputation: 25053

Start with an extra-verbose version, just for its clarity:

UPDATE TableA
SET multiple = 'YES'
WHERE match in (
    -- isolate the multiples
    SELECT match from (
        -- count the matches
        SELECT count(*) as c, match from TableB
        GROUP BY match ) x
    WHERE c > 1
)

With the HAVING clause, you can change this...

    SELECT match from (
        SELECT count(*) as c, match from TableB
        GROUP BY match ) x
    WHERE c > 1

...to this:

   SELECT match from TableB
   GROUP BY match
   HAVING count(*) > 1

So now we have:

UPDATE TableA
SET multiple = 'YES'
WHERE match in (
   SELECT match from TableB
   GROUP BY match
   HAVING count(*) > 1
)

I'm sure it can be made more compact, but I personally get confused by UPDATE statements containing non-obvious JOIN clauses, especially in the middle of the night when I get the call that "the database isn't working!"

Don't Make Me Think applies to coding, too.

Upvotes: 4

Braden
Braden

Reputation: 617

UPDATE tableA
SET multiple = 'YES'
FROM TableA AS a
JOIN (SELECT match FROM tableB GROUP BY match HAVING COUNT(*) > 1) AS b ON a.match = b.match

Upvotes: 0

Teja
Teja

Reputation: 13524

UPDATE TableA a
SET multiple='YES'
FROM Tablea a,(SELECT match FROM Tableb GROUP BY match HAVING COUNT(*)>1)b
WHERE a.match=b.match

Upvotes: -3

Related Questions