SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update 2nd Instance		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

StephenWilcox
Starting Member

9 Posts
Posted - 11/29/2012 :  10:54:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 11/29/2012 :  11:09:38  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Edited by - nigelrivett on 11/29/2012 11:12:06
Go to Top of Page

StephenWilcox
Starting Member

9 Posts
Posted - 11/29/2012 :  13:15:13  Show Profile  Reply with Quote
I am using SQLite, and it looks to have worked.
Go to Top of Page

StephenWilcox
Starting Member

9 Posts
Posted - 11/29/2012 :  14:03:58  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 11/29/2012 :  15:46:37  Show Profile  Reply with Quote
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?
Go to Top of Page

StephenWilcox
Starting Member

9 Posts
Posted - 11/29/2012 :  16:38:38  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 11/29/2012 :  21:27:52  Show Profile  Reply with Quote
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
Edited by - sodeep on 11/29/2012 21:28:36
Go to Top of Page

StephenWilcox
Starting Member

9 Posts
Posted - 11/30/2012 :  10:08:16  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 11/30/2012 :  10:22:02  Show Profile  Reply with Quote
No You just have to change @T with your original table.
Go to Top of Page

Howard43Willard
Starting Member

USA
8 Posts
Posted - 12/02/2012 :  20:21:30  Show Profile  Reply with Quote
I hope that I have got this correct?
Edited by - Howard43Willard on 12/02/2012 20:26:45
Go to Top of Page

StephenWilcox
Starting Member

9 Posts
Posted - 12/04/2012 :  20:45:21  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 12/04/2012 :  20:58:37  Show Profile  Reply with Quote
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)
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 12/04/2012 :  21:04:11  Show Profile  Reply with Quote
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
Go to Top of Page

StephenWilcox
Starting Member

9 Posts
Posted - 12/07/2012 :  15:49:42  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 12/07/2012 :  16:16:33  Show Profile  Reply with Quote
Then put it in temp table.
Go to Top of Page

StephenWilcox
Starting Member

9 Posts
Posted - 12/08/2012 :  08:24:33  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000