CODEWITHSUNDEEP
sqlsql-server
gaffcz
gaffcz

Reputation: 3479

SQL procedure - changing value of table column based on count on other table

I have two tables, property and component. component.id_property = property.id.

I'm trying to create a procedure, which counts components on selected property and if there are no components for selected property, it change property.id_state to 1. 

create PROCEDURE property_statement_change AS
BEGIN
declare @value int;

select 
    @value = count(c.value)
from
    component c
where
    c.id_property = 1
group by c.id_property

IF (@value = 0)
  UPDATE property
  SET id_state = 1
  WHERE property.id = 1
END

If I execute stored produre, it doesn't change anything, but select and also update works right. What I'm doing wrong?

Upvotes: 3

Views: 1823

Answers (1)

aF.
aF.

Reputation: 66757

Like stated on the comments, value is NULL instead and not 0, so here's the code:

create PROCEDURE property_statement_change AS
BEGIN
declare @value int;

select 
    @value = count(c.value)
from
    component c
where
    c.id_property = 1
group by c.id_property

IF (@value is NULL)
  UPDATE property
  SET id_state = 1
  WHERE property.id = 1
END

Upvotes: 2

Related Questions