Trying to update Packet.MSGState to P where if not R or D and it is Y in table Packet.MSGState
SQL is MS-Access var ODBC from c#
This is not undating all correct.

UPDATE Packet SET Packet.MSGState = 'P'
Where (MSGState <> 'R' and  MSGState <> 'D') and
Exists
  (Select  MSGTO.Selected
  From MSGTO
  Where MSGTO.MSGTO = Packet.MSGTO and  MSGTO.Selected = 'Y')

Thanks Kevin

Whis was working:
UPDATE Packet SET Packet.MSGState = P Where Exists (Select MSGTO.Selected From MSGTO Where MSGTO.MSGTO = Packet.MSGTO and Selected = Y ) But need it not to update if already R or D

Help please

Hm. That "exists" clause seems unnecessary; have you tried updating with a join instead?

Here's the general shape:

UPDATE p
SET p.<fields...>
FROM Packet p
JOIN MSGTO m ON m.MSGTO = p.MSGTO
WHERE <conditions...>

This is what I do with SQL Server; Access might want different syntax, but IIRC it will do this too.

got it to work:

UPDATE Packet
INNER JOIN MSGTO ON Packet.MSGTO = MSGTO.MSGTO
SET Packet.MSGState = 'P'
where (Packet.MSGState is null or
(Packet.MSGState<>'R' And Packet.MSGState<>'D') )
and MSGTO.Selected = 'Y' ;

Looks as if null was the problem

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.