内连接问题 [英] Inner Join Problem

问题描述

以下查询在访问中工作正常并完全符合我的要求

但是当我将其移植到SQL Server时出现语法错误

2000 。


-------------


UPDATE OrdersProducts INNER产品ONdersProducts.ProductID =

Products.ProductID SET Products.Stock =

Products.Stock-OrdersProducts.Quantity

WHERE OrdersProducts.OrderID = 63116

--------------


任何帮助将不胜感激


很多谢谢

解决方案

我觉得我以前的帖子失败了......所以这就是我所说的......

如果我的帖子重新出现，请忽略它。;）


---------- -------------------------------------------------- ----------------------------------


你可以''用这种方式使用UPDATE语句......

这是它的完整定义语法：

UPDATE

{

table_name WITH（< table_hint_limited [... n]）

| view_name

| rowset_function_limited

}

SET

{column_name = {expression |默认| NULL}

| @variable =表达

| @variable = column = expression} [，... n]


{{[FROM {< table_source} [，... n]]


[WHERE

< search_condition]}

|

[当前的流量

{{[GLOBAL] cursor_name} | cursor_variable_name}

]}

[OPTION（< query_hint [，... n]）]

因此，在你的情况下，正确的语法是：

更新产品

SET Products.Stock =产品。库存 - 订单产品。数量

来自

产品

INNER JOIN OrdersProducts ON OrdersProducts.ProductID =

Products.ProductID

WHERE

OrdersProducts.OrderID = 63116


------------------------------------ -------------------------------------------------- --------


享受，

$ b $bCédricDelNibbio
自2003年以来
MCP

MCAD .NET

MCTS SQL Server 2005
** @ bodykind.com aécrit：

以下查询在访问中工作正常，完全符合我的要求

但是我得到了一个语法将其移植到SQL Server时出错

2000.

-------------


UPDATE OrdersProducts INNER JOIN产品ON OrdersProducts.ProductID =

产品。产品ID SET Products.Stock =

Products.Stock-OrdersProducts.Quantity

WHERE OrdersProducts.OrderID = 63116


--- -----------


非常感谢任何帮助


非常感谢

您不能以这种方式使用UPDATE语句......

UPDATE

{

table_name WITH（< table_hint_limited [... n]）

| view_name

| rowset_function_limited

}

SET

{column_name = {expression |默认| NULL}

| @variable =表达

| @variable = column = expression} [，... n]


{{[FROM {< table_source} [，... n]]


[WHERE

< search_condition]}

|

[当前的流量

{{[GLOBAL] cursor_name} | cursor_variable_name}

]}

[OPTION（< query_hint [，... n]）]

在你的情况下，正确的语法将是：

更新产品

SET Products.Stock =产品。库存 - 订单产品。数量

来自

产品

INNER JOIN OrdersProducts ON OrdersProducts.ProductID =

Products.ProductID

WHERE

OrdersProducts.OrderID = 63116


享受。

$ b $bCédricDelNibbio

自2003年以来的MCP

MCAD。 NET

MCTS SQL Server 2005
in**@bodykind.com aécrit：

以下查询在访问中工作正常并且完全符合我的要求

但是当我将其移植到SQL时出现语法错误服务器

2000.


-------------


更新订单产品INNERPINcts上的INNER JOIN产品.ProductID =

产品。产品ID SET Products.Stock =

产品。库存 - 订单产品。数量

订购产品。订购产品编号：63116

- -------------


我们将非常感谢任何帮助


非常感谢

符合ANSI SQL标准的重写此查询将是：


更新产品

SET Stock = （

SELECT Products.Stock - OrdersProducts.Quantity

来自OrdersProducts

WHERE OrdersProducts.ProductID = Products.ProductID

AND OrdersProducts.OrderID = 63116

）

WHERE EXISTS（

SELECT *

来自OrdersProducts

WHERE OrdersProducts.ProductID = Products.ProductID

AND OrdersProducts.OrderID = 63116

）


因为这个是标准的SQL，它将在任何SQL Server安装上运行，

并且应该在任何现代RDBMS上运行。它甚至可能在MS Access中运行。


HTH，

Gert-Jan

" in ** @ bodykind.com" ;写道：

>

以下查询在访问中工作正常并完全符合我的要求

it然而，当我将它移植到SQL Server时，我收到语法错误

2000.


------------ -


UPDATE OrdersProducts INNER JOIN产品ON OrdersProducts.ProductID =

Products.ProductID SET Products.Stock =

Products.Stock -OrdersProducts.Quantity

WHERE OrdersProducts.OrderID = 63116


--------------


非常感谢任何帮助


非常感谢

The following query works fine in access and does exactly what I want
it to do however I get a syntax error when I port it over to SQL Server
2000.

-------------

UPDATE OrdersProducts INNER JOIN Products ON OrdersProducts.ProductID =
Products.ProductID SET Products.Stock =
Products.Stock-OrdersProducts.Quantity
WHERE OrdersProducts.OrderID=63116

--------------

Any help would be greatly appreciated

Many thanks

解决方案

Hi,

I feel like my previous post failed... So here is what I was saying...
Just ignore it if my post reappears.;)

----------------------------------------------------------------------------------------------

You can''t use the UPDATE statement this way...
Here is the complete definition of its syntax :
UPDATE
{
table_name WITH ( < table_hint_limited [ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression } [ ,...n ]

{ { [ FROM { < table_source } [ ,...n ] ]

[ WHERE
< search_condition ] }
|
[ WHERE CURRENT OF
{ { [ GLOBAL ] cursor_name } | cursor_variable_name }
] }
[ OPTION ( < query_hint [ ,...n ] ) ]
Thus, in your case, the correct syntax would be :
UPDATE Products
SET Products.Stock = Products.Stock - OrdersProducts.Quantity
FROM
Products
INNER JOIN OrdersProducts ON OrdersProducts.ProductID =
Products.ProductID
WHERE
OrdersProducts.OrderID = 63116

----------------------------------------------------------------------------------------------

Enjoy,

Cédric Del Nibbio
MCP since 2003
MCAD .NET
MCTS SQL Server 2005
in**@bodykind.com a écrit :

The following query works fine in access and does exactly what I want
it to do however I get a syntax error when I port it over to SQL Server
2000.

-------------

UPDATE OrdersProducts INNER JOIN Products ON OrdersProducts.ProductID =
Products.ProductID SET Products.Stock =
Products.Stock-OrdersProducts.Quantity
WHERE OrdersProducts.OrderID=63116

--------------

Any help would be greatly appreciated

Many thanks

Hi,

you may not use the UPDATE statement this way...
UPDATE
{
table_name WITH ( < table_hint_limited [ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression } [ ,...n ]

{ { [ FROM { < table_source } [ ,...n ] ]

[ WHERE
< search_condition ] }
|
[ WHERE CURRENT OF
{ { [ GLOBAL ] cursor_name } | cursor_variable_name }
] }
[ OPTION ( < query_hint [ ,...n ] ) ]
In your case, the proper syntax would be :
UPDATE Products
SET Products.Stock = Products.Stock - OrdersProducts.Quantity
FROM
Products
INNER JOIN OrdersProducts ON OrdersProducts.ProductID =
Products.ProductID
WHERE
OrdersProducts.OrderID = 63116

Enjoy.

Cédric Del Nibbio
MCP since 2003
MCAD .NET
MCTS SQL Server 2005
in**@bodykind.com a écrit :

The following query works fine in access and does exactly what I want
it to do however I get a syntax error when I port it over to SQL Server
2000.

-------------

UPDATE OrdersProducts INNER JOIN Products ON OrdersProducts.ProductID =
Products.ProductID SET Products.Stock =
Products.Stock-OrdersProducts.Quantity
WHERE OrdersProducts.OrderID=63116

--------------

Any help would be greatly appreciated

Many thanks

The ANSI SQL compliant rewrite of this query would be:

UPDATE Products
SET Stock = (
SELECT Products.Stock - OrdersProducts.Quantity
FROM OrdersProducts
WHERE OrdersProducts.ProductID = Products.ProductID
AND OrdersProducts.OrderID=63116
)
WHERE EXISTS (
SELECT *
FROM OrdersProducts
WHERE OrdersProducts.ProductID = Products.ProductID
AND OrdersProducts.OrderID=63116
)

Since this is standard SQL, it will run on any SQL Server installation,
and should run on any modern RDBMS. It might even run in MS Access.

HTH,
Gert-Jan
"in**@bodykind.com" wrote:

>
The following query works fine in access and does exactly what I want
it to do however I get a syntax error when I port it over to SQL Server
2000.

-------------

UPDATE OrdersProducts INNER JOIN Products ON OrdersProducts.ProductID =
Products.ProductID SET Products.Stock =
Products.Stock-OrdersProducts.Quantity
WHERE OrdersProducts.OrderID=63116

--------------

Any help would be greatly appreciated

Many thanks

这篇关于内连接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！