SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Check Tables for value in same column and report
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nferri
Starting Member

USA
5 Posts

Posted - 12/19/2012 :  15:43:37  Show Profile  Reply with Quote
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

Edited by - nferri on 12/19/2012 15:48:04

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/19/2012 :  22:10:53  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 12/19/2012 :  22:17:06  Show Profile  Reply with Quote
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/

Edited by - visakh16 on 12/19/2012 22:33:55
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000