If Exists Statement Problem...

Can someone give me a hand with this?

Ok I have a table called allstocks, with a primary key of ticker, I also have a todaysstocks table with a primary key of ticker.

Todaysstock table updates the allstocks table with this statement,

insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under]) select exchange,[todays date],ticker,[opened date], [closed date],[over/under] from todaysstocks

this works fine, unless the ticker already exists. How do I write the statement, that if the ticker already exists, then update the rest of the fields with the new info, or delete the row and recreate it?

ANy help would be appreciated. Thanksif exists (select 1 from allstock a inner join todaysstocks t on a.ticker=t.ticker)
delete a from allstock a inner join todaysstocks t on a.ticker=t.ticker
...|||insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under])
select exchange,[todays date],ticker,[opened date], [closed date],[over/under]
from todaysstocks a
where
ticker not in (select ticker from allstock b where b.ticker = a.ticker)
--|||This may be a hog...
insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under])
select exchange,[todays date],ticker,[opened date], [closed date],[over/under]
from todaysstocks a
where
ticker not in (select ticker from allstock b where b.ticker = a.ticker)
--
This one will use an index (if one exists) on ticker:

insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under])
select a.exchange,a.[todays date],a.ticker,a.[opened date], a.[closed date],a.[over/under]
from todaysstocks a left outer join allstocks b on a.ticker=b.ticker
where b.ticker is null

But this will not alter the data that is already in allstocks, rather it will insert 0 rows. The question was:

How do I write the statement, that if the ticker already exists, then update the rest of the fields with the new info, or delete the row and recreate it?|||You need to passes to accomplish this. The first statement updates existing rows, and the second statement adds new rows.

--Update existing records (you can eliminate fields that are part of the natural key):
update allstocks
set allstocks.exchange = todaysstocks.exchange,
allstocks.transdate = todaysstocks.[todays date],
allstocks.ticker = todaysstocks.ticker,
allstocks.[opened date] = todaysstocks.[opened date],
allstocks.[closed date] = todaysstocks.[closed date],
allstocks.[over/under] = todaysstocks.[over/under]
from allstocks
inner join todaysstocks on allstocks.keyfields = todaysstocks.keyfields

--Add new records:
insert into allstocks (exchange, transdate, ticker, [opened date], [closed date], [over/under])
select exchange, [todays date], ticker, [opened date], [closed date], [over/under]
from todaysstocks
left outer join allstocks on todaysstocks.keyfields = allstocks.keyfields
where allstocks.keyfields is null