Please Check my SQL Stmt 1

[JohnBates]

hi experts,

Can you find my mistake?
Thanks

** This finds 6,170 duplicate records and inserts them, works fine:

INSERT INTO dbo.FLIGHT_DATA_FACT_EXACT_DUPLICATES
SELECT AIRLINE_CODE, CARRIER_CODE, FLIGHT_DATE, FLIGHT_NUMBER
FROM dbo.FLIGHT_DATA_FACT
GROUP BY AIRLINE_CODE, CARRIER_CODE, FLIGHT_DATE, FLIGHT_NUMBER
HAVING COUNT(*) > 1;


** But this code, using same columns, does not update any records **:

UPDATE b
SET FLIGHT_DATA_ID = a.FLIGHT_DATA_ID, LAST_UPDATE = a.LAST_UPDATE
from FLIGHT_DATA_FACT_EXACT_DUPLICATES b
join FLIGHT_DATA_FACT a on
b.AIRLINE_CODE = a.AIRLINE_CODE
AND b.CARRIER_CODE = a.CARRIER_CODE
AND b.FLIGHT_DATE = a.FLIGHT_DATE
AND b.FLIGHT_NUMBER = a.FLIGHT_NUMBER;

0 rows updated

What is wrong with my UPDATE statement ?
Thanks.

 

[djj55]

What do you get from?

select b.FLIGHT_DATA_ID, a.FLIGHT_DATA_ID, b.LAST_UPDATE, a.LAST_UPDATE
from FLIGHT_DATA_FACT_EXACT_DUPLICATES b
join FLIGHT_DATA_FACT a on
b.AIRLINE_CODE = a.AIRLINE_CODE
AND b.CARRIER_CODE = a.CARRIER_CODE
AND b.FLIGHT_DATE = a.FLIGHT_DATE
AND b.FLIGHT_NUMBER = a.FLIGHT_NUMBER;

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[JohnBates]

Thanks for your reply, djj.

This is what I get: 21,816 rows similar to....

b.FLIGHT_DATA_ID, a.FLIGHT_DATA_ID, b.LAST_UPDATE, a.LAST_UPDATE
NULL 1092983 NULL 2009-08-13 15:52:24.400

 

[djj55]

One thing is I like to qualify the type of join Left/Right/Inner/Cross. I would assume you want a inner join in this case.

What your results tell me is that there should be 21,819 rows updated. Since this is not what you get/want I would first look into the join type. After that I am not sure.

Sorry I can not be of more help.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[Ferrian]

When you execute the Update query does it hang for a while before telling you 0 rows were updated, or go straight there?

Maybe your DB has a short timeout and the time taken to update that many rows is causing the function to die and rollback the changes made.

~Ben
Occasional sparks in a darkened room

 

[JohnBates]

Thanks djj55!

All I had to change was (as you suggested) to add the type of join.

Hmmm.. I assumed that SQL Server would use an inner join if the type was not specified

Thanks very much! John

This worked and updated the intended rows:

UPDATE b
SET FLIGHT_DATA_ID = a.FLIGHT_DATA_ID, LAST_UPDATE = a.LAST_UPDATE
from FLIGHT_DATA_FACT_EXACT_DUPLICATES b
inner join FLIGHT_DATA_FACT a on
b.AIRLINE_CODE = a.AIRLINE_CODE
AND b.CARRIER_CODE = a.CARRIER_CODE
AND b.FLIGHT_DATE = a.FLIGHT_DATE
AND b.FLIGHT_NUMBER = a.FLIGHT_NUMBER;