Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    6

    Unanswered: 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. Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    if 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
    ...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2004
    Posts
    4
    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)
    --

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This may be a hog...
    Quote Originally Posted by pgzzbriones
    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:
    Code:
    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?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •