How to update record in table

Question

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 27-May-21 4:14am

Member 15219030

Updated 2-Jun-21 5:50am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2021-06-02T05:50:00

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