Click here to Skip to main content
14,971,754 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
This is my query:-

Declare @StartDate DateTime='05/27/2021'
Declare @EndDate DateTime='05/29/2021'
Declare @Used int;
--UPDATE AgencyFlightSeats SET Seats=15
SELECT Used,heldseat,FKID_Reservations,agency from(SELECT AFS.Seats as heldseat ,AFS.FKID_Reservations,COUNT(PKID_PaxReservation) as Used,AFS.FKID_Agency as agency FROM Reservations R
INNER JOIN AgencyFlightSeats AFS ON R.PKID_Reservations=AFS.FKID_Reservations
INNER JOIN Agency a on a.PKID_Agency=AFS.FKID_Agency
LEFT OUTER JOIN PaxReservation PR ON PR.FKID_Reservation=R.PKID_Reservations
WHERE R.StartDateTime >=@StartDate AND R.StartDateTime<@EndDate
AND R.OperatingStatus >0 AND AFS.FKID_Agency NOT in(551, 443, 856, 906, 907, 3) GROUP BY AFS.Seats,AFS.FKID_Reservations,AFS.FKID_Agency) t1
2. After execute query:-
Used heldseat FKID_Reservations agency
0 10 6227 1
Output- Used-0
heldseat-10
fkid_Reseravtaion-6227
agency-1

3. I have another table(AgencyFlightSeats)
PKID_AgencyFlightSeats	FKID_Agency	 Seats	FKID_Reservations	DateCreated
              5848	           1	     10	        6227	         2021-05-27               
              5849	          551	     10	        6227	         2021-05-27 


4. What I want-
Need to update value- Used value with Seats value based on condition

What I have tried:

Declare @StartDate DateTime='05/27/2021'
Declare @EndDate DateTime='05/29/2021'
Declare @Used int;
UPDATE AgencyFlightSeats  SET Seats=Used
 SELECT Used,heldseat,FKID_Reservations,agency   from(SELECT AFS.Seats as heldseat ,AFS.FKID_Reservations,COUNT(PKID_PaxReservation) as Used,AFS.FKID_Agency as agency FROM Reservations R 
INNER JOIN AgencyFlightSeats AFS ON R.PKID_Reservations=AFS.FKID_Reservations
INNER JOIN Agency a on a.PKID_Agency=AFS.FKID_Agency
LEFT OUTER JOIN PaxReservation PR ON PR.FKID_Reservation=R.PKID_Reservations
 WHERE R.StartDateTime >=@StartDate AND R.StartDateTime<@EndDate 
AND R.OperatingStatus >0 AND AFS.FKID_Agency NOT in(551, 443, 856, 906, 907, 3)   GROUP BY AFS.Seats,AFS.FKID_Reservations,AFS.FKID_Agency) t1 where  t1.agency=t1.agency


Please help me
Thanks
Posted
Updated 2-Jun-21 5:50am

1 solution

Run this simple example to demonstrate why you need to make the changes I am going to suggest
SQL
-- some silly data
DECLARE @demo1 table (key1 int, data1 varchar(10));
insert into @demo1 (key1, data1) values (1, 'demo1 data');

-- some more silly data that links where @demo1.key1 = @demo2.key2
DECLARE @demo2 table (key2 int, data2 varchar(10));
insert into @demo2 (key2, data2) values (1, 'demo2 data');

select * from @demo1;

-- This is how you do updates from one table to another via a join
update d1 set data1 = data2
from @demo1 d1
inner join @demo2 d2 on d1.key1 = d2.key2;

select * from @demo2;
That simple example is meant to show you the structure of an update using joined tables.

You have extra selects and are missing a join
Try changing your query as follows...
SQL
UPDATE AFS2 SET Seats=t1.Used
 SELECT Used,heldseat,FKID_Reservations,agency   
from (SELECT AFS.Seats as heldseat ,AFS.FKID_Reservations,COUNT(PKID_PaxReservation) as Used,AFS.FKID_Agency as agency FROM Reservations R 
INNER JOIN AgencyFlightSeats AFS ON R.PKID_Reservations=AFS.FKID_Reservations
INNER JOIN Agency a on a.PKID_Agency=AFS.FKID_Agency
LEFT OUTER JOIN PaxReservation PR ON PR.FKID_Reservation=R.PKID_Reservations
 WHERE R.StartDateTime >=@StartDate AND R.StartDateTime<@EndDate 
AND R.OperatingStatus >0 AND AFS.FKID_Agency NOT in(551, 443, 856, 906, 907, 3)   GROUP BY AFS.Seats,AFS.FKID_Reservations,AFS.FKID_Agency) t1 
-- all of the above select should be referred to as t1
-- can't refer to AgencyFlightSeats that is in the sub-query so join to it again...
-- I've used AFS2 as the alias to show that it's this outer reference that we are using
inner join AgencyFlightSeats AFS2 ON AFS2.FKID_Reservations=t1.FKID_Reservations
where  t1.agency=t1.agency
-- That where clause is meaningless
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900