Please Check My SQL Stmt - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Please Check My SQL Stmt

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-04-23 : 17:53:02

hi experts,

Can you find my mistake?
Thanks

** This inserts 6,170 records:

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;

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-23 : 18:35:13

quote:
Originally posted by jbates99

hi experts,

Can you find my mistake?
Thanks

** This inserts 6,170 records:

INSERT INTO [dbo].[FLIGHT_DATA_FACT_EXACT_DUPLICATES]
SELECT AIRLINE_CODE, CARRIER_CODE, FLIGHT_DATE, FLIGHT_NUMBER;

** 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;

There is no FROM clasue on the First SELECT:

INSERT INTO [dbo].[FLIGHT_DATA_FACT_EXACT_DUPLICATES]
SELECT AIRLINE_CODE, CARRIER_CODE, FLIGHT_DATE, FLIGHT_NUMBER
FROM ??

Are we to assume that you are selecting from the FLIGHT_DATA_FACT and inserting that into the FLIGHT_DATA_FACT_EXACT_DUPLICATES table?

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-04-23 : 21:23:05

Thanks Lamprey. I didn't intend to make it so easy:)

I left out the From and gruop By clause.

This is the complete INSERT stmt - and it does work:

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;

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-04-25 : 22:34:53

Can anyone help please?
Thanks, John

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-25 : 22:38:14

any of the column AIRLINE_CODE, CARRIER_CODE, FLIGHT_DATE, FLIGHT_NUMBER contains NULL ?

KH
[spoiler]Time is always against us[/spoiler]

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-04-25 : 23:41:18

khtan,

I checked just now. No, none of the recs have nulls in an of these 4 columns.

Thanks for your reply.

John

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-26 : 00:02:05

Can you please execute the below statement and post the output

Select Count(1)
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;

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-26 : 10:58:04

you updating FLIGHT_DATA_FACT_EXACT_DUPLICATES where you original insert from FLIGHT_DATA_FACT thus no records will be updated because the data between these 2 table are same....

Hope can help...but advise to wait pros with confirmation...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-26 : 11:17:54

quote:
Originally posted by waterduck

you updating FLIGHT_DATA_FACT_EXACT_DUPLICATES where you original insert from FLIGHT_DATA_FACT thus no records will be updated because the data between these 2 table are same....

Hope can help...but advise to wait pros with confirmation...

No. SQL Server isn't that intelligent to decide if an update is not needed...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-26 : 11:19:28

Looks like you have created dbo.FLIGHT_DATA_FACT_EXACT_DUPLICATES and then inserted the grouped data.
Now, if the join isn't matching then have a look at the data types of the joining columns in both tables.
Are they the same data type?

No, you're never too old to Yak'n'Roll if you're too young to die.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-26 : 12:22:44

Just thought I'd point something out here. This isn't the cause of your problem, but it is something you should be aware of.

Your update statement will pick a random records from FLIGHT_DATA_FACT and use those to populate FLIGHT_DATA_ID and LAST_UPDATE in FLIGHT_DATA_FACT_EXACT_DUPLICATES. as there will be multiple rows in FLIGHT_DATA_FACT for each row in FLIGHT_DATA_FACT_EXACT_DUPLICATES, and you're updating FLIGHT_DATA_FACT_EXACT_DUPLICATES, there will be multiple source rows for each destination row.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-04-26 : 15:23:37

pk_bohra, thanks for your reply.

Select Count(1)
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;

21816

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-26 : 16:02:20

quote:
Originally posted by jbates99

pk_bohra, thanks for your reply.

Select Count(1)
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;

21816

Then the next question is, what makes you say there update statement isn't updating any records?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-04-26 : 21:31:16

Solved. Thanks to everyone who took the time to look at this.

All I had to change was to explicitly specify the TYPE of join I wanted. Then it updates the intended rows.

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

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;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 22:30:42

That isn't what solved it if you are using Microsoft SQL Server. JOIN == INNER JOIN. LEFT JOIN == LEFT OUTER JOIN. RIGHT JOIN == RIGHT OUTER JOIN.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources