0

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

Edited by NA7KR: Formatting fixed. Please indent code in the future.

2
Contributors
4
Replies
48
Views
2 Years
Discussion Span
Last Post by NA7KR
Featured Replies
  • Hm. That "exists" clause seems unnecessary; have you tried [updating with a join](http://sqlandme.com/2013/06/18/sql-server-update-table-with-inner-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 … Read More

0

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

Edited by NA7KR

1

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.

0

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

This question has already been answered. Start a new discussion instead.
Be sure to adhere to our posting rules.