Updating first record - 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

Updating first record

Author

Topic

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-03-16 : 15:06:42

I would like to update the odometers for the first record of each unit in Jan. Can anyone tell me how I should approach this to get it to work please? I have entered some sample data below along with the table names and fields.

Before
lgh_tractor lgh_startdate lgh_odometerstart lgh_odometerend
2601 2008-12-29 00:01:00.000 NULL NULL
2601 2008-12-29 00:02:00.000 NULL NULL
2601 2009-01-06 06:00:00.000 NULL NULL

After
lgh_tractor lgh_startdate lgh_odometerstart lgh_odometerend
2601 2008-12-29 00:01:00.000 481235 NULL
2601 2008-12-29 00:02:00.000 NULL NULL
2601 2009-01-06 06:00:00.000 NULL NULL

Table: legheader
Unit Field: lgh_tractor
Odometer Field: lgh_odometerstart
Date field: lgh_startsdate

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-16 : 15:08:17

Are you using SQL server 2005 or higher?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-16 : 15:12:36

Try this..

declare @t table (col1 int, col2 datetime, odo int)
insert @t
select 2601, '2008-12-29 00:01:00.000', NULL union all
select 2601, '2008-12-29 00:02:00.000', NULL union all
select 2601, '2009-01-06 06:00:00.000', NULL

update t set odo = 481235 from (select top 1 * from @t order by col2 asc) t

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-03-16 : 15:12:41

Yes

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-03-16 : 15:16:17

Sorry I meant to say I wanted to update each tractor with different odometers. The sample above was for tracor 2601. Can you tell me how I can update 1 tractor. I then can use an Excel file for the rest of the tracks after I get the syntax.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-16 : 15:31:27

You have the odometer values stored in an excel sheet for each tractor?

If you get them onto another table with tractor number and odometer value, then you can update like this...

declare @t table (col1 int, col2 datetime, odo int)
insert @t
select 2601, '2008-12-29 00:01:00.000', NULL union all
select 2601, '2008-12-29 00:02:00.000', NULL union all
select 2601, '2009-01-06 06:00:00.000', NULL union all
select 2602, '2008-12-29 00:01:00.000', NULL union all
select 2602, '2008-12-29 00:02:00.000', NULL union all
select 2602, '2009-01-06 06:00:00.000', NULL

declare @r table (col1 int, odo int)
insert @r
select 2601,481235 union all
select 2602,12345

update t set t.odo = r.odo from (select row_number() over (partition by col1 order by col2) as rn,col1,col2,odo from @t) t
inner join @r r on t.col1 = r.col1 and t.rn = 1

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-03-16 : 15:54:01

Here is what I would really like to do and I have done in the past that really works good. I just don’t know what syntax to use to get the first record. From the cell to the right of the data in Excel I would like to enter something like this then copy it down to the last record. Then I could just copy it into a query editor and run it. I just need the code to hard code 1 record

="UPDATE legheader SET odo = "&C2&" WHERE month"&B2&" = 1 and lgh_tractor = "&A2&" Whatever the rught syntax is"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 10:58:11

where will odometer values come from? excel cell?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources