[加急]SQL语法转Access-怎么转啊?

[Cwst]

客户表中的Region如果不是Null则更新

UPDATE Orders SET Orders.ShipRegion = IsNull(SELECT Customers.Region FROM Customers WHERE Customers.CustomerID = Orders.CustomerID,Orders.ShipRegion)
WHERE Orders.ShipRegion IS NULL

[OracleRoob]

--Access中的isnull(字段名)仅仅用来判断是否为空，与SQL Server的isnull(字段名,新值)不同。

--需要变通使用iif(isnulL(字段名),新值,字段名) 这种方式处理：


UPDATE Orders AS A
inner join Customers AS B on A.CustomerID = B.CustomerID
SET
A.ShipRegion = iif(isnull(B.Region),A.ShipRegion,B.Region)

[OracleRoob]

Access使用的是Jet-SQL，SQL Server使用的是T-SQL，两者用法上相差很大。


JET SQL 帮助(jet4 access2000)下载地址

http://www.access911.net/index.asp?board=8&recordid=75FAB71E&tt=

[OracleRoob]

如果在纯Access中使用，还可以用域函数处理，如一楼方法。

[wwwwb]

UPDATE Orders SET Orders.ShipRegion =
iif(IsNull(
dlookup('Region','Customers','CustomerID = ' & Orders.CustomerID)),
ShipRegion,dlookup('Region','Customers','CustomerID = ' & Orders.CustomerID))
WHERE Orders.ShipRegion IS NULL

[wwwwb]

上述假如'CustomerID 为数字型，如为字符型
UPDATE Orders SET Orders.ShipRegion =
iif(IsNull(
dlookup('Region','Customers','CustomerID = ' & Orders.CustomerID)),
ShipRegion,dlookup('Region','Customers','CustomerID = "' & Orders.CustomerID
& '"'))
WHERE Orders.ShipRegion IS NULL
DLOOKUP为VBA函数，只能在ACCESS中使用。

[wwwwb]

or
UPDATE Orders inner join Customers on Customers.CustomerID = Orders.CustomerID
SET Orders.ShipRegion = iif(isnull(Customers.Region),Orders.ShipRegion,Customers.Region)

[wwwwb]

or
UPDATE Orders inner join Customers on Customers.CustomerID = Orders.CustomerID
SET Orders.ShipRegion = iif(isnull(Customers.Region),Orders.ShipRegion,Customers.Region)WHERE Orders.ShipRegion IS NULL

[OracleRoob]

Access的update语句不支持子查询，需要用域函数。

[yuan731]

UPDATE Orders SET Orders.ShipRegion =
iif(IsNull(
dlookup('Region','Customers','CustomerID = ' & Orders.CustomerID)),
ShipRegion,dlookup('Region','Customers','CustomerID = ' & Orders.CustomerID))
WHERE Orders.ShipRegion IS NULL