By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,660 Members | 1,592 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,660 IT Pros & Developers. It's quick & easy.

Update Query (Access vs. SQL Server)

P: n/a
Can someome please advise what the equivalent query would be in
Microsoft SQL Server ... I've tried a number of combinations with no
success ... Thanks, Ralph Noble (ra*********@hotmail.com)

================

UPDATE INVENTORY

INNER JOIN SALES ON (INVENTORY.BAR_CODE = SALES.BAR_CODE)

AND (INVENTORY.PRODUCT_NBR = SALES.PRODUCT_NBR)

SET INVENTORY.DATE_PURCHASED = "20050127"
WHERE (((SALES.SOLD)="20050127"));

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
The UPDATE... FROM... JOIN syntax is a proprietary extension to the SQL
language and unfortunately the Access and SQLServer versions work
differently. SQLServer also supports ANSI SQL92 UPDATEs and I find the
ANSI-compatible syntax clearer and more logical. Try:

UPDATE Inventory
SET date_purchased = '20050127'
WHERE EXISTS
(SELECT *
FROM Sales AS S
WHERE S.bar_code = Inventory.bar_code
AND S.product_nbr = Inventory.product_nbr
AND S.sold = '20050127') ;

--
David Portas
SQL Server MVP
--
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.