Update based on max time less than current row

[craig322]

I need to do an update query that will update rows in a table based on a row in the table that was collected immediately prior to it.

So for example, say I have 4 rows. I have delimited them by | for ease of reading.


Row a: A|10:52|99
Row b: B|9:49|66
Row c: C|8:50|0
Row d: D|8:32|26

I need to update A based on B's value, B based on D's value (since c was 0) and leave d as it is since there is no earlier value.

TIA

 

[rac2]

It can be done, probably, but more information would help.

Give the names of the table and the columns.

Are there any values in the last column now? How did they get there?

Would it be feasible to calculate the value when you INSERT the row for the most recent observation?

Are these repeated observations on the same thing, or are these repeated observations on many things? In which case you must have another column which identifies the things. Is the column with A,B,C,D a primary key?

 

[dombeneteau]

Hi,

Do you have access to an 'identity' or 'autoincrement' feature? If so:

1. Populate a temp or work table with your set of data. The temp or work table has the same structure as yours, plus this extra column automatically incremented. Obviously, to populate your temp table, you use a select statement that orders your datas (order by) based on your decision column (the time one, in your case).

2. Now, you can update the first table (in one statement) by using the temp table twice in your from clause (alias) and adding a where clause including a condition formed with this extra column (where temp1.new_column = temp2.new_column + 1 or something like that).

... but if not, you still have the good old method (can be slow...):
(your col2 has been named time_col)

treat one row at a time... (RDBMS dependent or using a cursor <grin>)
repeat
update <a.what_you_want = f(b.what_you_want)>
from my_table a, my_table b
where a.time_col = max(a.time_col)
and b.time_col = max(b.time_col)
and b.time_col < a.time_col
until <a row has been updated> (RDBMS dependent)

Hope it helps

 

[rwheels]

as rac2 stated, more info would be helpful.

Sounds like you could use a trigger to accomplish your goal. Although, I'm operating under the assumption you're using MS SQL Server.

First, you need an auto-incrementing field. In this case I'll call it ID. And since you didn't supply a table name, I'll call it your_table.

You would use an &quot;on insert&quot; trigger. A trigger is an action or query that executes after predefined condition is met within the table that a trigger is created for. An on-insert trigger will fire whenever an insert happens....

your query within the trigger would be something like this (although you didn't really give much of a clue about what update you had in mind) :

update your_table
set X_field = Y
where ID = (select (max(ID) - 1) from your_table)

 

[craig322]

Thanks for all the help. Unfortunately I am programming this in teradata and do not have an autoincrement feature like I would in SQL server or Oracle. I found a way to do what I wanted using the following logic in a view then unioning it to the value at the first interval.

FROM dss_tables.branch_activity_stats b, dss_tables.branch_activity_stats a
LEFT OUTER JOIN sys_calendar.calendar as c
on a.date_loaded = c.calendar_date
WHERE a.date_loaded = b.date_loaded
AND a.branch_id = b.branch_id
AND a.collection_time > b.collection_time

 

[dnoeth]

&quot;Unfortunately I am programming this in teradata&quot;
You should be glad about that ;-)

This is a good example how to use OLAP functions.
No need for cursors or temp tables or slow nested subqueries ;-)

create table craig(c char, t time, v int);

ins craig('A','10:52',99);
ins craig('B','9:49',66);
ins craig('C','8:50',0);
ins craig('D','8:32',26);

update craig
from
(
select c as c2, t as t2, v, v- mdiff(v,1,t asc) as vnew
from craig
where v <> 0
qualify vnew is not null
) dt
set v = vnew
where c = c2
and t = t2;

Fast and efficient regardless of tablesize and as you run Teradata it's probably a large table.
But the main question is: Do you really have to update the data when you can easily calculate it during select? If the rows are updated, you'll have to rerun the update.

For your table you'll probably have to add a
GROUP BY date_loaded, branch_id

And as it's a DBMS specific question, why don't you post it on the Teradata forum?
forum328