Re: SQL stmts do not work on SQL server but work on Access

Perfect. Worked. Thanks!!

MGFoster wrote:
The statment is not an SQL standard statement. Access lets you do non
standard SQL. To convert to standard SQL (only one table name in the
UPDATE clause):

UPDATE ##service
SET status = 'Covered'
WHERE EXISTS (SELECT * FROM ##coverage AS C
WHERE ##service.dt_filled BETWEEN C.start_dt AND C.end_dt
AND id = ##service.mem)

The following statements does not work on Microsoft SQL server. (ERROR msg:
Incorrect syntax near the keyword 'LEFT'.) But they work on Access (with
[quoted text clipped - 4 lines]
WHERE (##service.dt_filled) Between ##coverage.START_DT And ##coverage.END_DT)
;

--
Message posted via http://www.accessmonster.com
.

Relevant Pages

  • Re: Inner Join Where Not Null
    ... Besides not being Standard SQL, ... CREATE TABLE Cities ... INSERT INTO Countries VALUES; ... The FROM clause gives you a CROSS JOIN. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL - As part of an aggregate function ERROR
    ... For similar reasons, I think it should be COUNT. ... In standard SQL, ... Specifying a column within the COUNT function forces the DBMS to ... anyhow and thus can be excluded in the WHERE clause. ...
    (microsoft.public.excel.programming)
  • Re: Outer join by (+)
    ... Robert Klemme wrote: ... I can read the standard SQL just fine, because that's what I'm used to. ... WHERE clause excludes. ... This still leaves the issue of lesser expressiveness for the proprietary Oracle syntax. ...
    (comp.databases.oracle.server)
  • Re: Another, more complex Update Statement
    ... letting the engine grab one at random. ... There is no FROM clause in a Standard SQL UPDATE statement; ... The UPDATE clause simply gives the name of the base table or updatable ... Notice that no correlation name is allowed in the UPDATE clause; ...
    (microsoft.public.sqlserver.programming)
  • Re: use And or Where to filter join table in Update statement?
    ... There is no FROM clause in a Standard SQL ... Notice that no correlation name is allowed in the UPDATE clause; ... The SET clause is a list of columns to be changed or made; ...
    (microsoft.public.sqlserver.programming)