SQL Query Help please

[NA7KR]

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

[NA7KR]

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

[NA7KR]

Help please

[gusano79]

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.

[NA7KR]

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