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)
 Check Tables for value in same column and report

Author  Topic 

nferri
Starting Member

5 Posts

Posted - 2012-12-19 : 15:43:37
If i had 4 tables:

FrogsTable: NamesColumn:bill,steve,john
DogsTable: NamesColumn: bob, bill, john
catsTable: NameColumn: steve, sam, ax
AnimalsNamedJohnTable:
AnimalsColumn: Frogs, Dogs, Cats
NamedJohnColumn: True, True, False

how can I run a check to see if I have any frogs named john and update the animalsNamedJohn table frogs row to True or (0)?

I esentailly want to check all my tables that have the same column for a specific value and have a different table record wether that value exists for that table. Please help. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 22:10:53
you have to do it in batches like


UPDATE a
SET a.Frogs='True'
FROM AnimalsNamedJohnTable a
WHERE EXISTS (SELECT 1 FROM FrogsTable WHERE NamesColumn = 'john')


UPDATE a
SET a.Dogs='True'
FROM AnimalsNamedJohnTable a
WHERE EXISTS (SELECT 1 FROM DogsTable WHERE NamesColumn = 'john')


UPDATE a
SET a.Catss='True'
FROM AnimalsNamedJohnTable a
WHERE EXISTS (SELECT 1 FROM CatsTable WHERE NamesColumn = 'john')



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 22:17:06
or if you've to do it in one update use

UPDATE a
SET a.Frogs=CASE WHEN FrogsCnt > 0 THEN 'True' ELSE 'False' END,
a.Dogs = CASE WHEN DogsCnt > 0 THEN 'True' ELSE 'False' END,
a.Cats = CASE WHEN CatsCnt > 0 THEN 'True' ELSE 'False' END
FROM AnimalsNamedJohnTable a
CROSS JOIN (
SELECT SUM(FrogsCnt) AS FrogsCnt,
SUM(DogsCnt) AS DogsCnt,
SUM(CatsCnt) AS CatsCnt
FROM
(SELECT COUNT(*) AS FrogCnt,CAST(0 AS int) AS DogsCnt,CAST(0 AS int) AS CatsCnt
FROM FrogsTable WHERE NamesColumn = 'john'
UNION ALL
SELECT 0,COUNT(*),0
FROM DogsTable WHERE NamesColumn = 'john'
UNION ALL
SELECT 0,0,COUNT(*)
FROM CatsTable WHERE NamesColumn = 'john'

)t
)r


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

- Advertisement -