Problems with Update join 1

[WarcraftPlayer]

I have an update query. That takes a group of documents and marks them as approved.

UPDATE documents 
SET		Approved = 1, 
		Imported = 1, 
		ApprovalDate = getdate(), 
		ApprovedBy = 'JOE', 
FROM documents d  
INNER JOIN customers c 
	ON d.customerid = c.customerid 
WHERE d.tempid ='AUTOPOST'

The problem I am having is there are previous documents that I want to change from approved(1) to not approved(0).

How do I write a query to take all the customers that came up in the above query and change all the other documents they have to not approved.

Thanks in advance.

 

[hneal98]

If I read you right, you want to take all records that were not marked as approved and mark them as not approved?

IF this is correct, just use the same update query and put in the where clause WHERE Approved = 1 and ...

This is a very simple solution, so you will have to tell us if this is what you wanted or not.

 

[WarcraftPlayer]

No I want to mark all previous records marked as not approved. This might help.

In the table even before I run the query above or anything gets scanned in I have the documents below.
Ex 1

Doc ID Customerid Approved ApprovalDate ApprovedBy CreateDate  
1      12345      1        6/7/2005     AAA        6/5/2005
8      12347      1        7/13/2005    FFF        7/13/2005
9      12366      1        11/23/2005   CCC        11/22/2005

Today I get a batch of documents and the are inserted into the table like so.
Ex 2

Doc ID Customerid Approved ApprovalDate ApprovedBy CreateDate
555    12345      null     null         null       1/12/2006
556    12366      null     null         null       1/12/2006

I run the above query it converts all the documents in Ex 2 to the following.
Ex 3

Doc ID Customerid Approved ApprovalDate ApprovedBy CreateDate
555    12345      1        1/12/2006    JJJ        1/12/2006
556    12366      1        1/12/2006    JJJ        1/12/2006

The question I have is I want to take all the previous documents the customer had Ex1 and change the approved to 0. So the data should now look like this when all is done. It might be as simple as adding somethign like approved = 1 but I tired that. When they are first scanned in I ID the new documents with an AUTOPOST in the tempid field but that is null after I run the above query. It must be some combonation between the AUTOPOST and the customerid I just cant get it.

Doc ID Customerid Approved ApprovalDate ApprovedBy CreateDate
1      12345      0        6/7/2005     AAA        6/5/2005
8      12347      1        7/13/2005    FFF        7/13/2005
9      12366      0        11/23/2005   CCC        11/22/2005
555    12345      1        1/12/2006    JJJ        1/12/2006
556    12366      1        1/12/2006    JJJ        1/12/2006

Hope this makes a little more sense.

 

[AtomicWedgie]

Maybe run this query (or something like this) before you update the new records with your update statement

update documents
set approved = 0
where customerid in (select distinct customerid
from documents where approved is null)
and approved = 1

Regards,

Atomic Wedgie

 

[hneal98]

Before adding the new documents, you could mark all old documents as not approved and then mark the new documents as approved after adding them.

You also have a create date on the documents. You could also use that to mark anything before a certain date as Not Approved.

 

[WarcraftPlayer]

Thanks combined both your suggestions and got what I needed thanks

/cry
/help

Sergeant Warkois
LVL 53 Paladin