Help needed on Transact - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help needed on Transact

Author

Topic

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-15 : 05:45:41

Hi there!

Need help on procedure.

There are 2 tables: "Competition_Images" & "Competitions" where I store the records.
Each time user adds an image to a competition I set the IsCompetitor (bit field) in Competition_Images table as true.
How can I set all of the fields IsCompetitor as false in "Competition_Images" in case I delete the competition from "Competitions" table?

I decided to select all ImageIDs from "Competition_Images" that have CompetitionID in to #tempTable
and then update "Competition_Images" IsCompetitor fields as 0 where ImageID = #tempTable.ImageID.
The level of my SQL experience letting me UPDATE only a single cell - not all of them! But I need kind of a batch Update....
Please Help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 05:48:52

[code]Update ci
SET ci.IsCompetitor=0
FROM Competition_Images ci
left join Competitions c
on c.CompetitionID=ci.CompetitionID
where CompetitionID IS NULL[/code]

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-15 : 06:32:22

quote:

Originally posted by visakh16

Update ci
SET ci.IsCompetitor=0
FROM Competition_Images ci
left join  Competitions c
on c.CompetitionID=ci.CompetitionID
where  CompetitionID IS NULL

Thank You Visakh! It work fine!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources