Проблемы с обновлением SQL в MS Access - операция должна использовать обновляемый запрос

У меня есть запрос select, который обрабатывает некоторые текстовые манипуляции, чтобы существенно переформатировать поле, чтобы я мог посмотреть его в другой таблице:

Если моя первая таблица, если у меня есть поле типа "J1/2", оно ищет идентификатор записи в другой таблице с J1 и J2 в соответствующих полях.

Все это хорошо работает.

Теперь я хочу обновить исходную таблицу, поэтому мне больше не нужно выполнять поиск с помощью этой обработки строк, но мои попытки запросов на обновления заканчиваются на "Операция должна использовать обновляемый запрос"

Любые идеи?

Оператор SELECT:

SELECT DISTINCT
t1.DD,
t1.TN,
t1.DD & " J" & MID(t1.TN,2,1) AS CalculatedStart,
t1.DD & " J" & MID(t1.TN,4,1) AS CalculatedEnd,
t2.ID
FROM t1 INNER JOIN t2
ON (t1.DD & " J" & MID(t1.TN,2,1)=t2.StartLink)
AND (t1.DD & " J" & MID(t1.TN,4,1)=t2.EndLink)
WHERE t1.TN Like "J?/?"
AND t1.DD Like "M*";

Вспомните - это отлично работает, и я получаю необходимый t2.ID с другого конца.

Итак, я хочу сделать что-то вроде:

UPDATE t1 SET t2ID = (
    SELECT Query1.ID
    FROM Query1
    WHERE t1.DD=Query1.DD
    AND t1.TN=Query1.TN
    )
WHERE t1.TN Like "J?/?"
AND t1.DD Like "M*";

Только это не удается. Это находится в самом MS Access, поэтому я не могу представить себе фактическую проблему с разрешениями, как, например, большинство проблем "Операция должна использовать обновляемый запрос".

EDIT: попытка упростить случай, который не работает.

Этот запрос UPDATE прекрасен:

UPDATE t1
SET t2ID="Unknown"
WHERE TN LIKE "J?/?"
AND DD LIKE "M*";

Это не удается (спасибо Goedke - этот пример явно терпит неудачу, потому что подзапрос возвращает более 1 результат. Я пытался упростить задачу)

UPDATE t1
SET t2ID=(SELECT ID FROM t2)
WHERE TN LIKE "J?/?"
AND DD LIKE "M*";

Так что я просто неправильно интерпретирую синтаксис подзапроса?

EDIT: этот оператор SELECT тоже хорош:

SELECT t1.OA, t1.DD, t1.TN, t1.HATRIS,
    query1.DD, query1.TN, query1.ID
FROM t1 INNER JOIN query1
ON t1.DD=query1.DD
AND t1.TN=query1.TN

Кроме того, используя счетчик вышеприведенного оператора select, показано, что в каждой (DD, TN) комбинации есть ровно 1 ID

EDIT:

Простейший случай, который я теперь получил - используя различные инструкции SELECT, теперь у меня есть таблица с двумя столбцами - первичный ключ t1 и значение, которое я хочу вставить в t1.

Я все еще не могу написать

UPDATE t1 SET t1.f2 = (SELECT t2.f2 FROM t2 WHERE t2.f1 = t1.f1)

где первичный ключ t1 - f1. Даже добавление WHERE t1.f1 IN (SELECT f1 FROM t2) не помогает. (Добавлено, чтобы исключить возможность того, что подзапрос возвращает 0 результатов)

Ответ 1

Подзапрос (SELECT ID FROM t2) не может работать, если в t2 нет только одной записи. Какой идентификатор вы ожидаете использовать?

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

Тот факт, что вы используете DISTINCT, заставит меня подозрительно, что подзапрос возвращает более одной строки в вашем более сложном примере. Это, вероятно, самая распространенная проблема с назначением результата подзапроса: недоустановка предложения where.

Другая проблема, которую я видел при назначении из подзапроса, - это неправильный синтаксис внутреннего запроса. По крайней мере, с концами SQL 2000 и 2005, процессор запросов будет бесшумно отказываться и возвращает NULL в таких случаях. (Это, насколько я могу судить, ошибка: я не вижу причин, по которым что-то, что вернет ошибку на верхнем уровне, будет разрешено без разрешения в подзапросе... но там оно есть.)

EDIT: Чтобы убедиться, что ни Павел, ни я не сходили с ума, я создал следующие таблицы:

t1 | ID, FK, Data
t2 | ID2, Data2

Я не использовал никаких ограничений, кроме первичного ключа ID и ID2. Все поля были текстом, который отличается от того, что я обычно использую для идентификаторов, но должен быть неактуальным.

t1:

ID  FK  Data
Key1        Data1
Key2        Data2
Key3        Data3

t2:

ID2 Data2
Key1    DataA
Key2    DataB
Key3    DataC

Запрос формы:

UPDATE t1 SET t1.FK = (select ID2 from t2 where t2.ID2 = t1.ID);

Не удалось получить то же сообщение, которое получил Пол.

select *, (select ID2 from t2 where t2.ID2 = t1.ID) as foreign from t1,

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

UPDATE t1 SET t1.FK = 'Key1'

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

Примечание. Если я изменил бэкэнд базы данных с native на SQL 2005, обновление будет работать! Немного googling вокруг, и я нахожу Access MVPs, предлагая DLOOKUP заменить подзапрос:

http://www.eggheadcafe.com/software/aspnet/31849054/update-with-subquerycomp.aspx

По-видимому, это ошибка в Access SQL, которую избегают при использовании SQL Express 2000 или выше. (Результаты Google для "подзапроса обновления доступа" поддерживают эту теорию).

См. здесь, как использовать это обходное решение: http://www.techonthenet.com/access/functions/domain/dlookup.php

Ответ 2

Я должен взвесить с Дэвидом У. Фентоном комментарий к OP.

Это очень раздражающая проблема с Jet/ACE. Но попробуйте либо:

перейдите к свойствам запроса (нажмите фон панели, где отображаются таблицы) и "Уникальные записи" на "Да"
Вариант 1 является эквивалентом добавления несколько странно выглядящий DISTINCTROW ключевое слово SELECT, например

UPDATE DISTINCTROW tblClient 
       INNER JOIN qryICMSClientCMFinite 
          ON tblClient.ClientID = qryICMSClientCMFinite.ClientID
   SET tblClient.ClientCMType = "F";

Это решает так много проблем, связанных с этим сообщением об ошибке, что это почти смешно.

Этот MS Access в двух словах - если вы не знаете обходной секрет для проблемы x, вы можете потратить несколько дней, пытаясь найти ответ. Чтобы узнать, 10 000 обходных путей для программирования Access. Достаточно ли этого предупреждения для непосвященных?

Бен

Ответ 3

Это работало для меня (Access 2000)

UPDATE DISTINCTROW T1 inner join T2 on T2.f1 = T1.f1  SET f2 = f2;

Ответ 4

Я не читал весь поток, но это решение, которое я использую:

update (select * from t1 inner join t2 on t1.key = t2.key) set t1.field1 = t2.field2

и это отлично работает в MS Access для меня.

Ответ 5

Мое решение состояло в том, чтобы изменить мой sql таким образом.

  update (select o.pricein, g.pricein from operations o left join goods g on g.id = o.goodid where o.opertype = 4 and o.acct = 1) 
  set o.pricein = g.pricein

Ответ 6

У меня была такая же ошибка ( "Операция должна использовать обновляемый запрос" ) с использованием Access 2010, и я выполнял простой запрос обновления с внутренним соединением. Все, что я сделал, это добавить первичный ключ к таблице, к которой я присоединился (уже был один, конечно, в таблице, которую я обновлял), и все сработало.

Ответ 7

Для этого: UPDATE t1 SET t1.f2 = (SELECT t2.f2 FROM t2 WHERE t2.f1 = t1.f1)

UPDATE t1 INNER JOIN t2 ON t1.f1 = t2.f1 SET t1.f2 = [t2].[f2];