Writing a new insert procedure

Question

Post reply

Writing a new insert procedure

austen.robinson

Right there with Babe

Points: 762
More actions
August 13, 2014 at 6:23 pm

#289908

I have the following two tables:
CREATE TABLE eventhistory (
Event_id number not null,
Event_Description varchar2(100),
Update_time date not null
);
CREATE TABLE currentevents (
Event_id number not null,
Event_Description varchar2(100
);
l have a primary key on event_id and a process that inserts a new row into the “eventhistory” table every time an event description is changed, with the current time as the update_time, I now need some SQL to populate the “currentevents” table with the latest Event_description for each event. Can anyone point me in the correct direction?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

austen.robinson Right there with Babe Points: 762 More actions · Answer 1

I've come up with this but need help on the conditions part.

UPDATE currentevents

SET Event_Description = (SELECT Event_Description

FROM eventhistory

WHERE conditions)

WHERE conditions;

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 2

austen.robinson (8/13/2014)
I have the following two tables:
CREATE TABLE eventhistory (
Event_id number not null,
Event_Description varchar2(100),
Update_time date not null
);
CREATE TABLE currentevents (
Event_id number not null,
Event_Description varchar2(100
);
l have a primary key on event_id and a process that inserts a new row into the “eventhistory” table every time an event description is changed, with the current time as the update_time, I now need some SQL to populate the “currentevents” table with the latest Event_description for each event. Can anyone point me in the correct direction?

Untested, of course, but I believe the following should be pretty close...

WITH

cteGetLatestEventHistory AS

(

SELECT Event_ID, Event_Description,

RowNum = ROW_NUMBER() OVER (PARTITION BY Event_ID ORDER BY Update_Time DESC)

FROM dbo.EventHistory

)

UPDATE cur

SET cur.Event_Description = hist.Event_Description

FROM dbo.currentevents cur

JOIN cteGetLatestEventHistory hist

ON cur.Event_ID = hist.Event_ID

WHERE hist.RowNum = 1

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

desperadomar Mr or Mrs. 500 Points: 539 More actions · Answer 3

I hope you are on the ORACLE platform seeing the DDL

FROM a SQL Server Point of view

If you created these table like these

CREATE TABLE eventhistory (

Event_id int not null,

Event_Description varchar(100),

Update_time datetime not null)

CREATE TABLE currentevents (

Event_id int not null,

Event_Description varchar(100))

Since varchar2 is not available in sql server I have used varchar and instead of DATE I have used DATETIME since the former will not have a time part

My question is How will you know your current event_description is outdated in current_tables. If you have just a single event in current_tables then it is not applicable.

update currentevents

set

Event_Description=dt.Event_Description

from (select event_id, event_description from eventhistory where Update_time=(select MAX(Update_time) from eventhistory))DT

inner join eventhistory e on e.Event_id=dt.Event_id

where e.Event_id=DT.Event_id

The derived table gets the event last updated (DT)and update the currentevents table. Jeff also shown the same using Common Table expression

(CTE)

If a currentevents also have DATE column to know if its is outdated from eventhistory, you can adjust the query to update all events with a 'where' condition

where currenteventUpdate_time<Update_time