CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
JavaScript Developer
JavaScript Developer

Reputation: 4008

Mixing an IF-statement in an UPDATE statement in SQL

I'm working on a SQL Server 2008 database. This database has two tables.

Book
ID
BookCategoryID
Name
IsFlagged

BookCategory
ID
Name
IsFlagged

I need to update the BookCategory table so that if any books in a category are flagged, the IsFlagged property on the BookCategory record is set to true. Otherwise, if there are not any books in a category that are flagged, I need to set the IsFlagged property on the BookCategory record to false. Is there a way to do this? I can't seem to figure it out. I was trying something like the following but kept hitting a dead-end.

UPDATE
  BookCategory
SET
  IsFlagged = (SELECT COUNT(*) FROM Book WHERE [IsFlagged]=1 AND [BookCategory]=?)

Upvotes: 2

Views: 6904

Answers (2)

JNK
JNK

Reputation: 65147

UPDATE BookCategory
SET IsFlagged = 0

UPDATE BookCategory b
SET IsFlagged = 1
WHERE EXISTS (SELECT 1 FROM Books
              WHERE bookCategoryid = b.id
              AND isFlagged = 1)

Upvotes: 3

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

UPDATE bc
SET IsFlagged = CASE WHEN b.ID IS NULL THEN 0 ELSE 1 END
FROM
    BookCategory bc LEFT JOIN
    Book b ON b.BookCategoryID = bc.ID AND b.IsFlagged = 1

Upvotes: 8

Related Questions