Issue with update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Issue with update query

Author

Topic

ravinder1483
Starting Member

9 Posts

Posted - 2011-01-08 : 07:57:04

Hi,

I am facing some issues while updating a table variable. Here goes the query:

UPDATE fd
SET fd.OutFlightNumber=
CASE WHEN fd.OutFlightDepDtTm=pd.FlightDepDtTm THEN pd.FlightNumber
ELSE fd.OutFlightNumber
END
,fd.InFlightNumber=
CASE WHEN fd.InFlightDepDtTm=pd.FlightDepDtTm THEN pd.FlightNumber
ELSE fd.InFlightNumber
END
FROM @FlightDetail fd JOIN @Passengerdetail pd
ON fd.ReservationID=pd.ReservationID

Here is the sample data:
@Passengerdetail contains following rows:
ReservationId FlightNumber FlightDepDtTm PAXCount InfantCount
277812591 5375 2011-02-08 1 0
277812591 5375 2011-02-09 1 0

Here is the ouput of Update statement:

ReservationId OutFlightNumber OutFlightDepDtTm InFlightNumber InFlightDepDtTm
277812591 5375 2011-02-08 NULL 2011-02-09

Now what confuses me is that why i am getting NULL in the column(InFlightNumber).
Please let me know whats the issue with this query.

Regards,

Ravinder Pal Singh

Ravinder Pal Singh
Email: ravinder1483@gmail.com

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 11:21:43

What does flight detail have for data for the ReservationID =277812591

Did you do a SELECT on the records to be sure the join worked properly ? Always test update statements by Selecting the results so you can view them. Make sure your select includes the joining columns on both sides (and do a full outer join so you can see where they don't match up)

Since you have TWO records which appear identically in your sample data, and you didn't present the row equivalent data for @Flightdetail, hard to say.

Do a select of the necessary fields and check the output.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-08 : 13:05:04

cant understand when this condition be true?
fd.InFlightDepDtTm=pd.FlightDepDtTm

as i see one table has only date and other only time. so it will be false and it will be always else condition that holds good. so if InFlightNumber was NULL before it will remain same value itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ravinder1483
Starting Member

9 Posts

Posted - 2011-01-08 : 22:57:40

Hi visakh16,

I have updated FlightDepDtTm data. Now please let me know is this query correct or i am doing something wrong.

Ravinder Pal Singh
Email: ravinder1483@gmail.com

ravinder1483
Starting Member

9 Posts

Posted - 2011-01-08 : 23:28:10

Hi dataguru1971,

@Flightdetail contains following data:
ReservationId OutFlightNumber OutFlightDepDtTm InFlightNumber InFlightDepDtTm
277812591 NULL 2011-02-08 NULL 2011-02-09

This data is just for one ReservationID for which i am getting unexpected result. This table does not contain any duplicate ReservationID.

I didn't get what do we mean by " Do a SELECT on the records to be sure the join worked properly" .
After Update statement, i used displayed the same columns in UPDATE query using the SELECT statement. What i got was the following:

SELECT query:

SELECT fd.ReservationID,OutFlightDepDtTm,CASE WHEN fd.OutFlightDepDtTm=pd.FlightDepDtTm THEN pd.FlightNumber
ELSE fd.OutFlightNumber
END 'OutFlightNumber'
,InFlightDepDtTm
,CASE WHEN fd.InFlightDepDtTm=pd.FlightDepDtTm THEN pd.FlightNumber
ELSE fd.InFlightNumber
END 'InFlightNumber'
FROM @FlightDetail fd JOIN @Passengerdetail pd
ON fd.ReservationID=pd.ReservationID

Output of this query is as follows:
ReservationId OutFlightNumber OutFlightDepDtTm InFlightNumber InFlightDepDtTm
277812591 5375 2011-02-08 NULL 2011-02-09
277812591 NULL 2011-02-08 5375 2011-02-09

Please help me.

Ravinder Pal Singh
Email: ravinder1483@gmail.com

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 23:44:04

You get TWO results with the select when you want one. You can SEE where the NULL result you don't want comes from

REplace:
CASE WHEN fd.OutFlightDepDtTm=pd.FlightDepDtTm THEN pd.FlightNumber
ELSE fd.OutFlightNumber

CASE WHEN ASE WHEN fd.OutFlightDepDtTm=pd.FlightDepDtTm THEN COALESCE (pd.FlightNumber,fd.FlightNumber) END

And add a group by to the select.

The key is you want only ONE result from the select statement you posted.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 01:09:01

I think i got your problem. You're required values are existing in two different records so using single join you wont get both values . the update has to be something like


UPDATE fd
SET	fd.OutFlightNumber= pd1.FlightNumber
,fd.InFlightNumber= pd2.FlightNumber
FROM @FlightDetail fd 
LEFT JOIN @Passengerdetail pd1
ON fd.ReservationID=pd1.ReservationID
AND fd.OutFlightDepDtTm=pd1.FlightDepDtTm
LEFT JOIN  @Passengerdetail pd2
ON fd.ReservationID=pd2.ReservationID
AND fd.InFlightDepDtTm=pd2.FlightDepDtTm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ravinder1483
Starting Member

9 Posts

Posted - 2011-01-09 : 10:29:34

Hi dataguru1971,

I used select statement only to check where the issue is.
OK, let me re-potray my question:

I have two rows with same ReservationID in table1 and only a single row in table2 with same ReservationID as in table1. Now if i run an update query with join between table1 and table2, then will update statement run only for one Row in table1 or both rows in table1?

Ravinder Pal Singh
Email: ravinder1483@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 12:52:33

did you try my suggestion at all?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ravinder1483
Starting Member

9 Posts

Posted - 2011-01-10 : 10:53:28

Yes visakh16.

Thanks for your suggestion. Its working now. But i still have a doubt which is bothering me a lot

I have two rows with same ReservationID in table1 and only a single row in table2 with same ReservationID as in table1. Now if i run an update query with join between table1 and table2, then will update statement run only for one Row in table1 or both rows in table1?

Ravinder Pal Singh
Email: ravinder1483@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 10:58:45

it will run for both rows in table1 and end up updating the same row in table2 twice. As you had that case statement in initial update to check for additional condition before setting the in out flight numbers, what happens is during each update only one of them holds good and other one will force the false condition ie. retain original value (NULL). so based on order of update, you will still end up with NULL in one of fields in your single record of your table2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources