Need UPDATE query help

I have a table that now holds data about operator actions. The table is
updated each night from a .csv using DTS. The problem is that the rows do no
t
include all the columns I need. I need to caculate a column from two other
rows and add it to the first. For Example, I am trying to add "New Column":
User Encoder1 Encoder2 Action
"New Column"
John 30 26354 7
26398-26354=44
John 30 26354 2
John 30 26398 3
The Encoder1 positions will always be the same as well as the user names
between the three rows. This table is large and I am going to need to change
all of the rows in this table and all the rows that are added each night. Is
this possible? any suggestions would be appreciated
ThanksI will make some guesses about which rows you are manipulating there, but
something like this will work
UPDATE u
SET u.NewColum = A3.Encoder2 - A2.Encoder2
FROM User u
INNER JOIN User A3 ON u.User = A3.User
INNER JOIN User A2 ON u.User = A2.User
WHERE u.User = 'John' AND u.Action = 7 AND A3.Action = 3 AND A2.Action = 2
I am assumingthat for the row with Action 7 you are calculating from tows
with Action 3 and 2. If not, then modify as you se fit, but I think the
query gets you in the right direction. You just need to alias the calls to
the same table to get the various rows.
HTH
John Scragg
"A.B." wrote:

> I have a table that now holds data about operator actions. The table is
> updated each night from a .csv using DTS. The problem is that the rows do
not
> include all the columns I need. I need to caculate a column from two other
> rows and add it to the first. For Example, I am trying to add "New Column"
:
> User Encoder1 Encoder2 Action
> "New Column"
> John 30 26354 7
> 26398-26354=44
> John 30 26354 2
> John 30 26398 3
> The Encoder1 positions will always be the same as well as the user names
> between the three rows. This table is large and I am going to need to chan
ge
> all of the rows in this table and all the rows that are added each night.
Is
> this possible? any suggestions would be appreciated
> Thanks|||>> I have a table that now holds data about operator actions. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
include all the columns I need. I need to caculate a column from two
other rows and add it to the first. <<
Let's get back to the basics of an RDBMS. there is no sequential
access or ordering in an RDBMS, so "first", "next" and "last" are
totally meaningless. If you want an ordering, then you need to havs a
column that defines that ordering in a column.
CREATE TABLE Foobar
(user_name CHAR(15) NOT NULL
encoder1 INTEGER NOT NULL,
encoder2 INTEGER NOT NULL,
foobar_action INTEGER NOT NULL,
PRIMARY KEY ('? ));
Unfortunately, you did not tell how to do the calculation. Would you
like to try again with a usable spec?