Как выполнить запрос на обновление с подзапросом в Access?

Я хочу перенести этот SQL-запрос, который прекрасно работает на SQL Server, MySQL и Oracle, в базу данных Access. Как я могу это сделать? Сейчас по какой-то причине мне запрашивает Company_ID.

Изменить: я получил сообщение, потому что забыл сначала создать столбец Company_ID в VendorRegKeys. Теперь я получаю сообщение об ошибке «Операция должна использовать обновляемый запрос».

UPDATE VendorRegKeys
   SET Company_ID = (SELECT Users.Company_ID
                     FROM Users
                     WHERE Users.User_ID = VendorRegKeys.CreatedBy_ID)

Обновление: я обнаружил, что это работает на основе ответа JuniorFlip:

UPDATE VendorRegKeys, Users
SET VendorRegKeys.Company_ID = Users.Company_ID
WHERE VendorRegKeys.CreatedBy_ID = Users.User_ID

sql syntax subquery ms-access
person Kip    schedule 03.09.2009    source источник
comment
Спасибо, это мне очень помогло!   -  person Daniel P. Bullington    schedule 04.08.2011
comment
Примечание. Если Users является запросом, а не таблицей и, следовательно, не подлежит обновлению, результат операции должен использовать обновляемый запрос.   -  person Corey Trager    schedule 21.12.2011

Ответы (3)


arrow_upward
13
arrow_downward

Это может быть связано с тем, что Company_ID не является существующим полем в VendorRegKeys OR Users.

РЕДАКТИРОВАТЬ:

UPDATE VendorRegKeys
INNER JOIN Users ON Users.User_ID = VendorRegKeys.CreatedBy_ID
SET VendorRegKeys.Company_ID = Users.Company_ID
person shahkalpeshp    schedule 03.09.2009
comment
ой, company_ID не существует в VendorRegKeys. Но после его добавления я теперь получаю, что операция должна использовать обновляемый запрос - person Kip; 03.09.2009
comment
не видел, чтобы вы отредактировали запрос, который работает... принимая ваш ответ, поскольку он действительно работает. Спасибо! - person Kip; 04.09.2009

arrow_upward
15
arrow_downward

Прямой ответ: нельзя. Простое ядро ​​базы данных Access не поддерживает стандартный синтаксис скалярного подзапроса SQL-92, даже если он находится в собственном так называемом режиме запросов ANSI-92.

Вы вынуждены использовать собственный проприетарный синтаксис, который не обеспечивает скалярное требование, т. е. небезопасен и будет выбирать значение произвольно и молча**. Кроме того, помимо простых конструкций, он вообще не работает, особенно там, где ваш подзапрос (если вам было разрешено использовать его в первую очередь) использует функцию набора (MAX, SUM и т. д.) - см. эту статью для некоторых действительно неудовлетворительных обходных путей.

Извините за негатив, но это действительно базовый синтаксис, и я не могу понять, почему команда Access еще не удосужилась его исправить. Это бесспорная причина номер один, по которой я больше не могу серьезно относиться к движку базы данных Access.

Чтобы продемонстрировать небезопасное поведение собственного синтаксиса UPDATE..JOIN..Set Access

CREATE TABLE Users
( 
  User_ID CHAR( 3 ) NOT NULL,
  Company_ID CHAR( 4 ) NOT NULL,
  UNIQUE ( Company_ID, User_ID ) );

CREATE TABLE VendorRegKeys
  CreatedBy_ID CHAR( 3 ) NOT NULL UNIQUE,
  Company_ID CHAR( 4 ) );

INSERT INTO Users VALUES ( 'Kip', 'MSFT' );
INSERT INTO Users VALUES ( 'Kip', 'AAPL' );

INSERT INTO VendorRegKeys VALUES ( 'Kip', NULL );

UPDATE VendorRegKeys
INNER JOIN Users ON Users.User_ID = VendorRegKeys.CreatedBy_ID
SET VendorRegKeys.Company_ID = Users.Company_ID;

При выполнении оператора обновления в Access пользовательский интерфейс предупреждает, что мы

Вы собираетесь обновить 2 строки.

несмотря на то, что в таблице VendorRegKeys всего одна строка!

На практике происходит только одно из значений, которые мы будем использовать для обновления столбца в этой единственной строке, без надежного способа предсказать, какое именно это будет значение.

При использовании скалярного синтаксиса подзапроса стандартного SQL вы получите ошибку, и оператор не сможет выполниться, что, возможно, является желаемой функциональностью (так же ведет себя и синтаксис MERGE стандартного SQL).

person onedaywhen    schedule 04.09.2009
comment
спасибо за всю информацию Я никогда не относился к доступу серьезно, но руководство говорит, что мы все равно должны его поддерживать. :-/ По крайней мере, в этом случае есть работающий альтернативный синтаксис. - person Kip; 04.09.2009

arrow_upward
8
arrow_downward

ты мог бы попробовать этот

update a
set a.company_id = b.company_id
from vendorRegkeys a, users b
where a.createdby_id = b.user_id
person JuniorFlip    schedule 03.09.2009
comment
хм .. это дает ошибку: синтаксическая ошибка (отсутствует оператор) в выражении запроса «b.company_id from vendorRegkeys a» - person Kip; 03.09.2009
comment
я заставил его работать с немного измененной версией этого, обновленной в вопросе. Спасибо! - person Kip; 03.09.2009