CODEWITHSUNDEEP
sqlsql-servert-sql
user4973915
user4973915

Reputation:

How can I make count() as a condition when I update a table?

SELECT COUNT(TableID) 
FROM DiningTable
WHERE IsAvail = 1;

I can get the "Number of Table" logically, however I want to use that number in the next sql query.

UPDATE DiningTable
SET IsAvail = 0
WHERE TableID = 11 - [Should be the number of Table, the result I get above];

What can I do?

Upvotes: 1

Views: 59

Answers (2)

Cmbuffa
Cmbuffa

Reputation: 151

make sure to put the isnull() check to avoid a null error if the first select return 0 rows

UPDATE DiningTable
SET IsAvail = 0
WHEN TableID = 11 - (SELECT ISNULL(COUNT(TableID),0) FROM DiningTable WHERE IsAvail = 1);

Upvotes: 3

ventik
ventik

Reputation: 887

UPDATE DiningTable
SET IsAvail = 0
WHEN TableID = 11 - (SELECT COUNT(TableID) FROM DiningTable WHERE IsAvail = 1);

Upvotes: 2

Related Questions