UPDATE using a join and NOT EXISTS?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Paul (paulhodgson24_at_hotmail.com)
Date: 07/07/04 

Date: 7 Jul 2004 06:12:29 -0700

Hi all,

I have two tables in a database, one of which I'm trying to update
based on the (non) existence of data in the other. Table one is a
staging table containing "real-time" data for a "To-Do" task list,
where data is inserted, updated and deleted frequently. Table two
contains identical data for reporting, except that it is a historical
version of the first table, and also contains an additional field to
flag whether or not the task is complete. When a task is complete, it
is deleted from the first table, but not from the second - what I need
to do, is update the second table to flag that particular task as
complete. I've tried a couple of different methods, but can't seem to
get it right! I assumed I could just use a relatively simple UPDATE
statement, containing a NOT EXISTS clause as follows:

UPDATE Table2
SET TaskComplete = 1
WHERE
NOT EXISTS (
  SELECT *
FROM Table2 AS t2 INNER JOIN table1 AS t1
ON t2.Task_ID = t1.Task_ID
)

I'm relatively new to the more complex aspects of T-SQL, so it's
probably no surprise to you that this doesn't work! I'm a bit lost on
this one, so any push in the right direction would be gratefully
received!

Thanks for any help,

Paul.

Relevant Pages

  • Re: CommonDialog returns wrong context
    ... I tried setting the flag: it does not solve my problem. ... Probably because my app is calling the 'Print' CommonDialog (for which the ... connectionstrings are pointing to a database in the current path. ... connectionstring in the form load procedure does not help because of the Ado ...
    (microsoft.public.vb.bugs)
  • Re: What is a surrogate identifier
    ... The "existence" of an address is something we rarely deal with. ... Springfield IL is an "undeliverable address". ... Presuming that the database behind the USPS website is accurate in this ... That's quite different from simply asserting that "such an address ...
    (comp.databases.theory)
  • Re: Finding last record in month for each of several types of reco
    ... used to flag the records which are the "Last Record of the Month" ... "Richard Buttrey" wrote: ... >>In your database, ... >>secondary sort key and the Date as the 3rd sort key. ...
    (microsoft.public.excel.programming)
  • Re: Send email
    ... I just want it to send automatically once i open the database without ... calls your email sub and set that macro to run automatically at startup. ... After the Send/Display call, you'll set that flag to True to ...
    (microsoft.public.access.formscoding)
  • Re: Changing auto-Negotiate settings
    ... In Smitty ethernet --> Change / Show Characteristics of an Ethernet Adapter ... The change can be made to the database with the -P flag. ... Subject: Changing auto-Negotiate settings ...
    (AIX-L)