G,
How about something like this:
CREATE TABLE Result(
a int NOT NULL,
b char(1) NOT NULL,
c char(10) NOT NULL
constraint PK_Result primary key(a, b)
)
CREATE TABLE History (
a int NOT NULL,
b char(1) NOT NULL,
c char(10) NOT NULL
constraint PK_History primary key(a, b)
)
CREATE TABLE Worklist (
a int NOT NULL,
b char(1) NOT NULL
constraint PK_WorkList primary key(a, b)
)
insert Result values (1, 'a', 'aaa')
insert Result values (2, 'b', 'bbb')
insert History values (1, 'a', 'zzz')
insert History values (3, 'c', 'ccc')
insert History values (4, 'd', 'ddd')
insert WorkList values (1, 'a')
insert WorkList values (2, 'b')
insert WorkList values (3, 'c')
insert WorkList values (4, 'd')
select * from Result
select * from History
select * from WorkList
delete History
from History
join WorkList
on History.a = WorkList.a
and History.b = WorkList.b
where not exists (select * from Result
where WorkList.a = Result.a
and WorkList.b = Result.b)
update History set c = Result.c
from History join Result
on History.a = Result.a
and History.b = Result.b
insert History (a, b, c)
select a, b, c
from Result
where not exists (select * from History
where History.a = Result.a
and History.b = Result.b)
drop table Result
drop table History
drop table WorkList
Linda
Quote:> I have three tables:
> CREATE TABLE History (
> a int NOT NULL,
> b char(1) NOT NULL,
> c char(10) NOT NULL)
> CREATE TABLE Worklist (
> a int NOT NULL,
> b char(1) NOT NULL)
> CREATE TABLE Result(
> a int NOT NULL,
> b char(1) NOT NULL,
> c char(10) NOT NULL)
> The Primary Key on all three is (a,b)
> Worklist contains a copy of all keys that are affected. Result
> contains the results of some calculations. Not all members of Worklist
> are represented in Results.
> I need to
> 1) Delete from History where a record is in Worklist but not in Result
> 2) Amend History to reflect the contents of Result (UPDATE where
> EXISTS, Else INSERT)
> So three operations in all: DELETE, UPDATE, INSERT. There seems to be
> many ways to achieve each of these operations. For example, For Step
> 2) I could use a further column in Result which indicates whether an
> Update or Insert to History is needed. This could be updated
> beforehand by joining Result to History (to find the UPDATEs). However
> I get the feeling this extra step can be avoided.
> Can anyone comment on some of the better ways to acheive what I
> require(where "better" equals quicker, I guess).
by