CODEWITHSUNDEEP
sqlt-sqlinsertsql-server-2008-r2
Aleksei Grebenschikov
Aleksei Grebenschikov

Reputation: 48

How to insert BIT value if row has relation

I have 3 tables BusStop, BusRoute, and Stop_Route (for M-2-M relation). Some stops do not have relation (routes), and I need to update each record in BusStop table with Bit value 1 or 0, depending on whether it has relation or not. I have a query to select all stops which have no relations: 

SELECT
    BusStop.StopId 
FROM
    BusStop
    LEFT OUTER JOIN BusStop_BusRoute 
    ON BusStop.StopId = BusStop_BusRoute.StopId
WHERE 
    BusStop_BusRoute.StopId IS NULL

but I don't clearly understand how to add a value based on this result. I've read about CURSOR and CASE WHEN statements, but I still can't figure out how to apply them in my case. There is a StopStatus column type of Bit where I need to insert that value.

Upvotes: 3

Views: 1468

Answers (2)

Sasa Popovic
Sasa Popovic

Reputation: 53

You can do something like

UPDATE BusStop SET BitValue = 0 WHERE StopID in 
(
--your query
SELECT
    BusStop.StopId 
FROM
    BusStop
    LEFT OUTER JOIN BusStop_BusRoute 
    ON BusStop.StopId = BusStop_BusRoute.StopId
WHERE 
    BusStop_BusRoute.StopId IS NULL
)

Here's (I think) complete code that's similar to yours. One table. Last query will set bit to 1 when i is 1.

create table aaa(id int identity, i int, j int, b bit)

insert into aaa values(1, 0, 0), (0, 0, 0), (0, 1, 0), (0, 0, 0)

select * from aaa

update aaa set b = 1 where id in (
select id from aaa where i = 1)

Upvotes: 0

Kofi Sarfo
Kofi Sarfo

Reputation: 3360

UPDATE BusStop
SET StopStatus = 
    CASE 
        WHEN BusStop_BusRoute.StopID IS NULL THEN 0 
        ELSE 1 
    END
FROM 
    BusStop
    LEFT JOIN BusStop_BusRoute 
    ON BusStop.StopId = BusStop_BusRoute.StopId

Upvotes: 3

Related Questions