Update 2nd Instance - 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

Update 2nd Instance

Author

Topic

StephenWilcox
Starting Member

9 Posts

Posted - 2012-11-29 : 10:54:44

Hello,

New to SQL. Basic syntax knowledge. Trying to do two updates to single field in table below:

AIRCRAFTID OPERATOR REG FULLTYPE MSN AIR DATE
1126_00009 Aircraft Trust & Financing Corp N200AX IAI 1126 Gulfstream G200 9 NAS 2011-07-04
1126_00014 Starship Enterprise Leasing LLC N121GV Gulfstream G200 14 LAS 2004-03-05
1126_00014 Sunstate Aviation And Leasing Llc N467MW IAI 1126 Gulfstream G200 14 YVR 2006-09-02
1126_00015 NIC Air Inc N622SV IAI 1126 Gulfstream G200 15 YVR 2003-09-20
1126_00016 ? N35BP IAI 1126 Gulfstream G200 16 FLL 2011-02-17
1126_00017 Wilmington Trust Co/James D Wolfensohn N48GX IAI 1126 Gulfstream G200 17 LAS 2004-03-05
1126_00019 BCOM Air LLC/Corporation Service Co N219AX IAI 1126 Gulfstream G200 19 FXE 2012-01-09
1126_00021 SmithfieldFoodsInc N321SF IAI 1126 Gulfstream G200 21 NAS 2012-06-10
1126_00022 Bank of America N414KD IAI 1126 Gulfstream G200 22 YVR 2005-06-11
1126_00022 Studio City Aviation 05 B Llc N322AD IAI 1126 Gulfstream G200 22 VNY 2008-10-24
1126_00023 John Borden N414DK IAI 1126 Gulfstream G200 23 YVR 2004-06-19
1126_00023 ? N32TM IAI 1126 Gulfstream G200 23 NAS 2011-02-06
1126_00026 Franklin P Johnson Jr N800PJ IAI 1126 Gulfstream G200 26 YVR 2002-03-02

Update 1 is to set Make = 0, where MIN(date) of AIRCRAFTID (first instance of AIRCRAFTID

SELECT *, MIN(date)
FROM AIRLOG2012
GROUP BY AIRCRAFTID,
ORDER BY AIRCRAFTID, DATE, REG;

This query worked, but I am trying to work out the 2nd update query

Update 2 is to set Make = 1, where MIN(date) of AIRCRAFTID (next instance(s) of AIRCRAFTID where REG is different.

I hope that I have got this correct?

Thanks,

Stephen

I would like to have

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-29 : 11:09:38

Are you sure you are using sql server? That query should give you an error - and it's not an update.

try
select *, make = -1 + row_number() over (partition by AIRCRAFTID order by date)
from tbl

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

StephenWilcox
Starting Member

9 Posts

Posted - 2012-11-29 : 13:15:13

I am using SQLite, and it looks to have worked.

StephenWilcox
Starting Member

9 Posts

Posted - 2012-11-29 : 14:03:58

I am really new to this. I guess my previous update query set all MAKE rows to 0. So lets start again. I am working with sqlite.

Here is some sample data:

AIRCRAFTID REG FULLTYPE DATE
A320000713 HB-IPU Airbus A319-112 2002-06-04
A320000714 N714AW Airbus A320-214 1998-11-22
A320000714 G-BXKA Airbus A320-214 2000-09-09
A320000714 G-BXKA Airbus A320-214 2001-12-01
A320000716 N716AW Airbus A320-214 1998-11-28
A320000716 G-BXKB Airbus A320-214 2000-09-10
A320000716 G-BXKB Airbus A320-214 2002-06-01
A320000716 G-BXKB Airbus A320-214 2002-06-08
A320000716 G-OMYA Airbus A320-214 2007-10-29
A320000719 C-FZUJ Airbus A319-114 1998-01-10
A320000719 C-FZUJ Airbus A319-114 1998-03-06
A320000719 C-FZUJ Airbus A319-114 1998-05-18
A320000719 C-FZUJ Airbus A319-114 2002-06-11
A320000719 C-FZUJ Airbus A319-114 2012-08-10
A320000721 C-FZUL Airbus A319-114 1997-11-23
A320000721 C-FZUL Airbus A319-114 2001-04-21
A320000721 C-FZUL Airbus A319-114 2002-05-11
A320000721 C-FZUL Airbus A319-114 2002-05-25
A320000721 C-FZUL Airbus A319-114 2002-06-15
A320000721 C-FZUL Airbus A319-114 2005-02-11
A320000721 C-FZUL Airbus A319-114 2005-04-02
A320000723 D-AILR Airbus A319-114 2011-04-22

Here is the syntax I used to search for the 1st date instance for each AIRCRAFTID group:

SELECT *, MIN(date)
FROM AIRLOG2012
GROUP BY AIRCRAFTID
ORDER BY AIRCRAFTID, DATE, REG;

This returns correct info and number of rows. So, I would like to update MAKE to 0 based on this search syntax.

Then I would like to do a 2nd update to MAKE = 1 based on this search syntax.

SELECT *, MIN(date)
FROM AIRLOG2012
GROUP BY AIRCRAFTID, REG
ORDER BY AIRCRAFTID, DATE, REG;

Where any subsequent REG changes for a unique AIRCRAFTID would set MAKE = 1.

I would like it to look like the following:

AIRCRAFTID REG FULLTYPE DATE MAKE
A320000713 HB-IPU Airbus A319-112 2002-06-04 0
A320000714 N714AW Airbus A320-214 1998-11-22 0
A320000714 G-BXKA Airbus A320-214 2000-09-09 1
A320000714 G-BXKA Airbus A320-214 2001-12-01
A320000716 N716AW Airbus A320-214 1998-11-28 0
A320000716 G-BXKB Airbus A320-214 2000-09-10 1
A320000716 G-BXKB Airbus A320-214 2002-06-01
A320000716 G-BXKB Airbus A320-214 2002-06-08
A320000716 G-OMYA Airbus A320-214 2007-10-29 1
A320000719 C-FZUJ Airbus A319-114 1998-01-10 0
A320000719 C-FZUJ Airbus A319-114 1998-03-06
A320000719 C-FZUJ Airbus A319-114 1998-05-18
A320000719 C-FZUJ Airbus A319-114 2002-06-11
A320000719 C-FZUJ Airbus A319-114 2012-08-10
A320000721 C-FZUL Airbus A319-114 1997-11-23 0
A320000721 C-FZUL Airbus A319-114 2001-04-21
A320000721 C-FZUL Airbus A319-114 2002-05-11
A320000721 C-FZUL Airbus A319-114 2002-05-25
A320000721 C-FZUL Airbus A319-114 2002-06-15
A320000721 C-FZUL Airbus A319-114 2005-02-11
A320000721 C-FZUL Airbus A319-114 2005-04-02
A320000723 D-AILR Airbus A319-114 2011-04-22 0

I hope that provides a more clear picture and more info.

Thanks again. Sorry for any confusion.

Stephen

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-29 : 15:46:37

quote:
Originally posted by StephenWilcox

I am really new to this. I guess my previous update query set all MAKE rows to 0. So lets start again. I am working with sqlite.

Here is some sample data:

AIRCRAFTID REG FULLTYPE DATE
A320000713 HB-IPU Airbus A319-112 2002-06-04
A320000714 N714AW Airbus A320-214 1998-11-22
A320000714 G-BXKA Airbus A320-214 2000-09-09
A320000714 G-BXKA Airbus A320-214 2001-12-01
A320000716 N716AW Airbus A320-214 1998-11-28
A320000716 G-BXKB Airbus A320-214 2000-09-10
A320000716 G-BXKB Airbus A320-214 2002-06-01
A320000716 G-BXKB Airbus A320-214 2002-06-08
A320000716 G-OMYA Airbus A320-214 2007-10-29
A320000719 C-FZUJ Airbus A319-114 1998-01-10
A320000719 C-FZUJ Airbus A319-114 1998-03-06
A320000719 C-FZUJ Airbus A319-114 1998-05-18
A320000719 C-FZUJ Airbus A319-114 2002-06-11
A320000719 C-FZUJ Airbus A319-114 2012-08-10
A320000721 C-FZUL Airbus A319-114 1997-11-23
A320000721 C-FZUL Airbus A319-114 2001-04-21
A320000721 C-FZUL Airbus A319-114 2002-05-11
A320000721 C-FZUL Airbus A319-114 2002-05-25
A320000721 C-FZUL Airbus A319-114 2002-06-15
A320000721 C-FZUL Airbus A319-114 2005-02-11
A320000721 C-FZUL Airbus A319-114 2005-04-02
A320000723 D-AILR Airbus A319-114 2011-04-22

Here is the syntax I used to search for the 1st date instance for each AIRCRAFTID group:

SELECT *, MIN(date)
FROM AIRLOG2012
GROUP BY AIRCRAFTID
ORDER BY AIRCRAFTID, DATE, REG;

This returns correct info and number of rows. So, I would like to update MAKE to 0 based on this search syntax.

Then I would like to do a 2nd update to MAKE = 1 based on this search syntax.

SELECT *, MIN(date)
FROM AIRLOG2012
GROUP BY AIRCRAFTID, REG
ORDER BY AIRCRAFTID, DATE, REG;

Where any subsequent REG changes for a unique AIRCRAFTID would set MAKE = 1.

I would like it to look like the following:

AIRCRAFTID REG FULLTYPE DATE MAKE
A320000713 HB-IPU Airbus A319-112 2002-06-04 0
A320000714 N714AW Airbus A320-214 1998-11-22 0 1
A320000714 G-BXKA Airbus A320-214 2000-09-09 1 1
A320000714 G-BXKA Airbus A320-214 2001-12-01
A320000716 N716AW Airbus A320-214 1998-11-28 0 1
A320000716 G-BXKB Airbus A320-214 2000-09-10 1 1
A320000716 G-BXKB Airbus A320-214 2002-06-01
A320000716 G-BXKB Airbus A320-214 2002-06-08
A320000716 G-OMYA Airbus A320-214 2007-10-29 1
A320000719 C-FZUJ Airbus A319-114 1998-01-10 0 1
A320000719 C-FZUJ Airbus A319-114 1998-03-06
A320000719 C-FZUJ Airbus A319-114 1998-05-18
A320000719 C-FZUJ Airbus A319-114 2002-06-11
A320000719 C-FZUJ Airbus A319-114 2012-08-10
A320000721 C-FZUL Airbus A319-114 1997-11-23 0 1
A320000721 C-FZUL Airbus A319-114 2001-04-21
A320000721 C-FZUL Airbus A319-114 2002-05-11
A320000721 C-FZUL Airbus A319-114 2002-05-25
A320000721 C-FZUL Airbus A319-114 2002-06-15
A320000721 C-FZUL Airbus A319-114 2005-02-11
A320000721 C-FZUL Airbus A319-114 2005-04-02
A320000723 D-AILR Airbus A319-114 2011-04-22 0 1
I hope that provides a more clear picture and more info.

Thanks again. Sorry for any confusion.

Stephen

Shouldn't above also be 1?

StephenWilcox
Starting Member

9 Posts

Posted - 2012-11-29 : 16:38:38

quote:
Originally posted by sodeep

quote:
Originally posted by StephenWilcox

I am really new to this. I guess my previous update query set all MAKE rows to 0. So lets start again. I am working with sqlite.

Here is some sample data:

AIRCRAFTID REG FULLTYPE DATE
A320000713 HB-IPU Airbus A319-112 2002-06-04
A320000714 N714AW Airbus A320-214 1998-11-22
A320000714 G-BXKA Airbus A320-214 2000-09-09
A320000714 G-BXKA Airbus A320-214 2001-12-01
A320000716 N716AW Airbus A320-214 1998-11-28
A320000716 G-BXKB Airbus A320-214 2000-09-10
A320000716 G-BXKB Airbus A320-214 2002-06-01
A320000716 G-BXKB Airbus A320-214 2002-06-08
A320000716 G-OMYA Airbus A320-214 2007-10-29
A320000719 C-FZUJ Airbus A319-114 1998-01-10
A320000719 C-FZUJ Airbus A319-114 1998-03-06
A320000719 C-FZUJ Airbus A319-114 1998-05-18
A320000719 C-FZUJ Airbus A319-114 2002-06-11
A320000719 C-FZUJ Airbus A319-114 2012-08-10
A320000721 C-FZUL Airbus A319-114 1997-11-23
A320000721 C-FZUL Airbus A319-114 2001-04-21
A320000721 C-FZUL Airbus A319-114 2002-05-11
A320000721 C-FZUL Airbus A319-114 2002-05-25
A320000721 C-FZUL Airbus A319-114 2002-06-15
A320000721 C-FZUL Airbus A319-114 2005-02-11
A320000721 C-FZUL Airbus A319-114 2005-04-02
A320000723 D-AILR Airbus A319-114 2011-04-22

Here is the syntax I used to search for the 1st date instance for each AIRCRAFTID group:

SELECT *, MIN(date)
FROM AIRLOG2012
GROUP BY AIRCRAFTID
ORDER BY AIRCRAFTID, DATE, REG;

This returns correct info and number of rows. So, I would like to update MAKE to 0 based on this search syntax.

Then I would like to do a 2nd update to MAKE = 1 based on this search syntax.

SELECT *, MIN(date)
FROM AIRLOG2012
GROUP BY AIRCRAFTID, REG
ORDER BY AIRCRAFTID, DATE, REG;

Where any subsequent REG changes for a unique AIRCRAFTID would set MAKE = 1.

I would like it to look like the following:

AIRCRAFTID REG FULLTYPE DATE MAKE
A320000713 HB-IPU Airbus A319-112 2002-06-04 0
A320000714 N714AW Airbus A320-214 1998-11-22 0 1
A320000714 G-BXKA Airbus A320-214 2000-09-09 1 1
A320000714 G-BXKA Airbus A320-214 2001-12-01
A320000716 N716AW Airbus A320-214 1998-11-28 0 1
A320000716 G-BXKB Airbus A320-214 2000-09-10 1 1
A320000716 G-BXKB Airbus A320-214 2002-06-01
A320000716 G-BXKB Airbus A320-214 2002-06-08
A320000716 G-OMYA Airbus A320-214 2007-10-29 1
A320000719 C-FZUJ Airbus A319-114 1998-01-10 0 1
A320000719 C-FZUJ Airbus A319-114 1998-03-06
A320000719 C-FZUJ Airbus A319-114 1998-05-18
A320000719 C-FZUJ Airbus A319-114 2002-06-11
A320000719 C-FZUJ Airbus A319-114 2012-08-10
A320000721 C-FZUL Airbus A319-114 1997-11-23 0 1
A320000721 C-FZUL Airbus A319-114 2001-04-21
A320000721 C-FZUL Airbus A319-114 2002-05-11
A320000721 C-FZUL Airbus A319-114 2002-05-25
A320000721 C-FZUL Airbus A319-114 2002-06-15
A320000721 C-FZUL Airbus A319-114 2005-02-11
A320000721 C-FZUL Airbus A319-114 2005-04-02
A320000723 D-AILR Airbus A319-114 2011-04-22 0 1
I hope that provides a more clear picture and more info.

Thanks again. Sorry for any confusion.

Stephen

Shouldn't above also be 1?

Hey sodeep. Thanks for the help. My goal is the 1) identify the when I first saw the AIRCRAFTID 2) Identify any changes in REG for that same AIRCRAFTID. See below.

I first saw the aircraft (A320000716) as N716AW on 1998-11-28. Then it was re-registered as G-BXKB, and I saw it a few more times as G-BXKB; then it was re-registered again as G-OMYA. So, I would like to mark the 1st time I saw A320000716 (0), and then hopefully identify subsequent registration changes such as G-BXKB (1), G-OMYA (1). Any sightings/records where the REG does not change can remain null.

A320000716 N716AW Airbus A320-214 1998-11-28 0
A320000716 G-BXKB Airbus A320-214 2000-09-10 1
A320000716 G-BXKB Airbus A320-214 2002-06-01
A320000716 G-BXKB Airbus A320-214 2002-06-08
A320000716 G-OMYA Airbus A320-214 2007-10-29 1

For AIRCRAFTID A320000721, I have seen it multiple times, but as the same REG. So, only the 1st instance needs to have MAKE = 0.

A320000721 C-FZUL Airbus A319-114 1997-11-23 0
A320000721 C-FZUL Airbus A319-114 2001-04-21
A320000721 C-FZUL Airbus A319-114 2002-05-11
A320000721 C-FZUL Airbus A319-114 2002-05-25
A320000721 C-FZUL Airbus A319-114 2002-06-15
A320000721 C-FZUL Airbus A319-114 2005-02-11
A320000721 C-FZUL Airbus A319-114 2005-04-02

I have 68,000 records, so I hope I can accomplish this without too much coding.

Thanks again,

Stephen

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-29 : 21:27:52

Here you go


Declare @T Table 
(
 TPK INT IDENTITY(1,1) PRIMARY KEY,
 AIRCRAFTID Varchar(100),
 REG Varchar(100),
 FULLTYPE Varchar(100),
 [DATE] DateTime,
 MAKE bit
 )

Insert into @T 
(AIRCRAFTID,REG,FULLTYPE,DATE)
Select 'A320000713' ,'HB-IPU' ,'Airbus A319-112' ,'2002-06-04' UNION ALL
Select 'A320000714' ,'N714AW' ,'Airbus A320-214' ,'1998-11-22' UNION ALL
Select 'A320000714' ,'G-BXKA' ,'Airbus A320-214' ,'2000-09-09' UNION ALL
Select 'A320000714' ,'G-BXKA' ,'Airbus A320-214' ,'2001-12-01' UNION ALL
Select 'A320000716' ,'N716AW' ,'Airbus A320-214' ,'1998-11-28' UNION ALL
Select 'A320000716' ,'G-BXKB' ,'Airbus A320-214' ,'2000-09-10' UNION ALL
Select 'A320000716' ,'G-BXKB' ,'Airbus A320-214' ,'2002-06-01' UNION ALL
Select 'A320000716' ,'G-BXKB' ,'Airbus A320-214' ,'2002-06-08' UNION ALL
Select 'A320000716' ,'G-OMYA' ,'Airbus A320-214' ,'2007-10-29' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'1998-01-10' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'1998-03-06' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'1998-05-18' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'2002-06-11' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'2012-08-10' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'1997-11-23' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2001-04-21' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2002-05-11' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2002-05-25' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2002-06-15' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2005-02-11' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2005-04-02' UNION ALL
Select 'A320000723' ,'D-AILR' ,'Airbus A319-114' ,'2011-04-22'

;WITH CTE as 
(
	SELECT
		*,
		ROW_NUMBER() OVER(PARTITION BY AIRCRAFTID ORDER BY Date)Seq,
		ROW_NUMBER() OVER(PARTITION BY AIRCRAFTID,REG ORDER BY Date)Seq1
	FROM @T
)


Update @T
Set t.MAKE = Case When Seq = 1 then 0 Else 1 End
from @T t
inner join CTE c on c.TPK = t.TPK
Where Seq = 1 OR (Seq1 = 1 and Seq <> Seq1)

Select * from @T
Order by TPK

StephenWilcox
Starting Member

9 Posts

Posted - 2012-11-30 : 10:08:16

quote:

Originally posted by sodeep

Here you go


Declare @T Table 
(
 TPK INT IDENTITY(1,1) PRIMARY KEY,
 AIRCRAFTID Varchar(100),
 REG Varchar(100),
 FULLTYPE Varchar(100),
 [DATE] DateTime,
 MAKE bit
 )

Insert into @T 
(AIRCRAFTID,REG,FULLTYPE,DATE)
Select 'A320000713' ,'HB-IPU' ,'Airbus A319-112' ,'2002-06-04' UNION ALL
Select 'A320000714' ,'N714AW' ,'Airbus A320-214' ,'1998-11-22' UNION ALL
Select 'A320000714' ,'G-BXKA' ,'Airbus A320-214' ,'2000-09-09' UNION ALL
Select 'A320000714' ,'G-BXKA' ,'Airbus A320-214' ,'2001-12-01' UNION ALL
Select 'A320000716' ,'N716AW' ,'Airbus A320-214' ,'1998-11-28' UNION ALL
Select 'A320000716' ,'G-BXKB' ,'Airbus A320-214' ,'2000-09-10' UNION ALL
Select 'A320000716' ,'G-BXKB' ,'Airbus A320-214' ,'2002-06-01' UNION ALL
Select 'A320000716' ,'G-BXKB' ,'Airbus A320-214' ,'2002-06-08' UNION ALL
Select 'A320000716' ,'G-OMYA' ,'Airbus A320-214' ,'2007-10-29' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'1998-01-10' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'1998-03-06' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'1998-05-18' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'2002-06-11' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'2012-08-10' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'1997-11-23' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2001-04-21' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2002-05-11' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2002-05-25' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2002-06-15' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2005-02-11' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2005-04-02' UNION ALL
Select 'A320000723' ,'D-AILR' ,'Airbus A319-114' ,'2011-04-22'

;WITH CTE as 
(
	SELECT
		*,
		ROW_NUMBER() OVER(PARTITION BY AIRCRAFTID ORDER BY Date)Seq,
		ROW_NUMBER() OVER(PARTITION BY AIRCRAFTID,REG ORDER BY Date)Seq1
	FROM @T
)


Update @T
Set t.MAKE = Case When Seq = 1 then 0 Else 1 End
from @T t
inner join CTE c on c.TPK = t.TPK
Where Seq = 1 OR (Seq1 = 1 and Seq <> Seq1)

Select * from @T
Order by TPK

sodeep. Thank you. I have a few more AIRCRAFTID's to update prior to updating the table. So, one last dumb question: Do I need enter all of the data for the 67,000 rows into the query as formatted above to update all the rows?

Thanks again,

Stephen

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-30 : 10:22:02

No You just have to change @T with your original table.

Howard43Willard
Starting Member

8 Posts

Posted - 2012-12-02 : 20:21:30

I hope that I have got this correct?

StephenWilcox
Starting Member

9 Posts

Posted - 2012-12-04 : 20:45:21

quote:
Originally posted by sodeep

No You just have to change @T with your original table.

Hello,

I am getting an error near "Declare": syntax error. Again. Thanks for the help. Here is my code:

Declare AIRLOG2012 Table
(
TPK INT IDENTITY(1,1) PRIMARY KEY,
TYPE Varchar(100),
OP Varchar(100),
AIRCRAFTID Varchar(100),
OPERATOR Varchar(100),
REG Varchar(100),
FULLTYPE Varchar(100),
MSN Varchar(100),
AIR Varchar(100),
[DATE] DateTime,
FN Varchar(100),
NOTES Varchar(100),
EVENT Varchar(100),
FLIGHT Varchar(100),

MAKE bit
)

Insert into AIRLOG2012
(TYPE, OP, AIRCRAFTID, OPERATOR, REG, FULLTYPE, MSN, AIR, FN, NOTES, EVENT, FLIGHT,)
Select 'A320000713' ,'HB-IPU' ,'Airbus A319-112' ,'2002-06-04' UNION ALL
Select 'A320000714' ,'N714AW' ,'Airbus A320-214' ,'1998-11-22' UNION ALL
Select 'A320000714' ,'G-BXKA' ,'Airbus A320-214' ,'2000-09-09' UNION ALL
Select 'A320000714' ,'G-BXKA' ,'Airbus A320-214' ,'2001-12-01' UNION ALL
Select 'A320000716' ,'N716AW' ,'Airbus A320-214' ,'1998-11-28' UNION ALL
Select 'A320000716' ,'G-BXKB' ,'Airbus A320-214' ,'2000-09-10' UNION ALL
Select 'A320000716' ,'G-BXKB' ,'Airbus A320-214' ,'2002-06-01' UNION ALL
Select 'A320000716' ,'G-BXKB' ,'Airbus A320-214' ,'2002-06-08' UNION ALL
Select 'A320000716' ,'G-OMYA' ,'Airbus A320-214' ,'2007-10-29' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'1998-01-10' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'1998-03-06' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'1998-05-18' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'2002-06-11' UNION ALL
Select 'A320000719' ,'C-FZUJ' ,'Airbus A319-114' ,'2012-08-10' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'1997-11-23' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2001-04-21' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2002-05-11' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2002-05-25' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2002-06-15' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2005-02-11' UNION ALL
Select 'A320000721' ,'C-FZUL' ,'Airbus A319-114' ,'2005-04-02' UNION ALL
Select 'A320000723' ,'D-AILR' ,'Airbus A319-114' ,'2011-04-22'

;WITH CTE as
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY AIRCRAFTID ORDER BY Date)Seq,
ROW_NUMBER() OVER(PARTITION BY AIRCRAFTID,REG ORDER BY Date)Seq1
FROM AIRLOG2012
)

Update AIRLOG2012
Set t.MAKE = Case When Seq = 1 then 0 Else 1 End
from AIRLOG2012 t
inner join CTE c on c.TPK = t.TPK
Where Seq = 1 OR (Seq1 = 1 and Seq <> Seq1)

Select * from AIRLOG2012
Order by TPK

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-04 : 20:58:37

You just need this:

;WITH CTE as 
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY AIRCRAFTID ORDER BY Date)Seq,
ROW_NUMBER() OVER(PARTITION BY AIRCRAFTID,REG ORDER BY Date)Seq1
FROM AIRLOG2012
)


Update t
Set t.MAKE = Case When Seq = 1 then 0 Else 1 End
from AIRLOG2012 t
inner join CTE c on c.<Primary Key>= t.<Primary Key>
Where Seq = 1 OR (Seq1 = 1 and Seq <> Seq1)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-04 : 21:04:11

You don't need to declare @t, SODEEP just did that since you didn't provide any DDL or usable sample data. If you already have a table AIRLOG2012, just replace @T in SODEEP's query with AIRLOG2012
and you should be good to go

;WITH CTE as
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY AIRCRAFTID ORDER BY Date)Seq,
ROW_NUMBER() OVER(PARTITION BY AIRCRAFTID,REG ORDER BY Date)Seq1
FROM AIRLOG2012
)

Update T
Set t.MAKE = Case When Seq = 1 then 0 Else 1 End
from AIRLOG2012 t
inner join CTE c on c.TPK = t.TPK
Where Seq = 1 OR (Seq1 = 1 and Seq <> Seq1)

Select * from AIRLOG2012
Order by TPK

Jim

Everyday I learn something that somebody else already knew

StephenWilcox
Starting Member

9 Posts

Posted - 2012-12-07 : 15:49:42

So, It looks like I might have to try another option, as sqlite does not support CTE queries I just discovered. Bummer.

Thanks

Stephen,

quote:
Originally posted by jimf

You don't need to declare @t, SODEEP just did that since you didn't provide any DDL or usable sample data. If you already have a table AIRLOG2012, just replace @T in SODEEP's query with AIRLOG2012
and you should be good to go

;WITH CTE as
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY AIRCRAFTID ORDER BY Date)Seq,
ROW_NUMBER() OVER(PARTITION BY AIRCRAFTID,REG ORDER BY Date)Seq1
FROM AIRLOG2012
)

Update T
Set t.MAKE = Case When Seq = 1 then 0 Else 1 End
from AIRLOG2012 t
inner join CTE c on c.TPK = t.TPK
Where Seq = 1 OR (Seq1 = 1 and Seq <> Seq1)

Select * from AIRLOG2012
Order by TPK

Jim

Everyday I learn something that somebody else already knew

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-07 : 16:16:33

Then put it in temp table.

StephenWilcox
Starting Member

9 Posts

Posted - 2012-12-08 : 08:24:33

Hello Sodeep,

Thanks for the assistance. It is working with the temp table setup and a couple of queries. Next, I am hoping that I might be able to use the Begin Transaction syntax to stack the multiple queries.

Thanks again,

Stephen

quote:
Originally posted by sodeep

Then put it in temp table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources