If values in Column A are the same and other values are equal to, then put X in another column-CodePudding

I have something like this...

FlightNum	Class	Seats
10	A	0
10	B	5
10	C	15
10	D	25

I want to write a statement where FlightNum is the same, and A class has 0 seats, all rows for that FlightNum should put a "1" in the SoldOut column.

FlightNum	Class	Seats	SoldOut
10	A	0	1
10	B	5	1
10	C	15	1
10	D	25	1

I'm not really sure where to start other than case statements, so I'm looking for some guidance.

CodePudding user response：

We check if Class = 'A' and Seats = 0 and then we use count window function separated by FlightNum to give the result to the entire flight.

select  *
       ,count(case when Class = 'A' and Seats = 0 then 1 end) over(partition by FlightNum) as SoldOut
from    t

FlightNum	Class	Seats	SoldOut
10	A	0	1
10	B	5	1
10	C	15	1
10	D	25	1

Fiddle

CodePudding user response：

Not sure about it, though my take here is that you're looking for an UPDATE statement, and probably don't even need the CASE construct, as you can put your conditions inside the WHERE clause.

WITH cte AS (
    SELECT FlightNum FROM tab WHERE Class = 'A' AND Seats = 0
)
UPDATE tab
SET SoldOut = 1
FROM       tab 
INNER JOIN cte
        ON tab.FlightNum = cte.FlightNum

Check the demo here.