Блог

Главная - Вопросы по программированию - SQL-запрос вызывает синтаксическую ошибку время выполнения 3144

SQL-запрос вызывает синтаксическую ошибку время выполнения 3144

#sql-server #tsql #ms-access #sql-update

#sql-server #tsql #ms-access #sql-обновление

Вопрос:

Следующая строка SQL возвращает ошибку из-за синтаксиса (ошибка времени выполнения 3144).

 st_sql = "UPDATE [tblProjectPlanB01], [tblProjectPlanFonts] SET [tblProjectPlanB01].[Week1Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week2Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week3Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week4Actual] = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week5Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week6Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week7Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week8Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week9Actual] = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week10Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week11Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week12Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week13Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week14Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week15Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week16Actual = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week17Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week18Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week19Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week20Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week21Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week22Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week23Actual = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week24Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week25Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week26Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week27Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week28Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week29Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week30Actual = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week31Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week32Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week33Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week34Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week35Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week36Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week37Actual = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week38Actual] = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week39Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week40Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week41Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week42Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week43Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week44Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week45Actual = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week46Actual] = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week47Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week48Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week49Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week50Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week51Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week52Actual] = [tblProjectPlanFonts].[Start] " amp; _
        "WHERE ((([tblProjectPlanB01].[StartWeekActual]) <= 1 And ([tblProjectPlanB01].[StartWeekActual]) <> 0) And (([tblProjectPlanFonts].[Select]) = '1')) "
        Application.DoCmd.RunSQL (st_sql)

Комментарии:

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

2. Стандартный синтаксис SQL для UPDATE примерно такой: UPDATE table_name SET column_name = value [, column_name = value ...] [WHERE condition] где после ключевого слова может быть только ОДНО имя таблицы UPDATE

3. Я обновляю только одну таблицу (tblPRojectlanB01), однако я ссылаюсь на значение из таблицы (tblProjectPlanFonts). Когда я запускаю его в запросе MS Access, он работает нормально

Ответ №1:

 st_sql = "UPDATE [tblProjectPlanB01]" amp; _
        "SET [tblProjectPlanB01].[Week1Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week2Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week3Actual] = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week4Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week5Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week6Actual] = [tblProjectPlanFonts].[Start], " amp; _
         "[tblProjectPlanB01].[Week7Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week8Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week9Actual] = [tblProjectPlanFonts].[Start], " amp; _
         "[tblProjectPlanB01].[Week10Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week11Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week12Actual] = [tblProjectPlanFonts].[Start], " amp; _
         "[tblProjectPlanB01].[Week13Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week14Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week15Actual] = [tblProjectPlanFonts].[Start], " amp; _
         "[tblProjectPlanB01].[Week16Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week17Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week18Actual] = [tblProjectPlanFonts].[Start], " amp; _
         "[tblProjectPlanB01].[Week19Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week20Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week21Actual] = [tblProjectPlanFonts].[Start], " amp; _
         "[tblProjectPlanB01].[Week22Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week23Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week24Actual] = [tblProjectPlanFonts].[Start], " amp; _
         "[tblProjectPlanB01].[Week25Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week26Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week27Actual] = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week28Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week29Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week30Actual] = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week31Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week32Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week33Actual] = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week34Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week35Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week36Actual] = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week37Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week38Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week39Actual] = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week40Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week41Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week42Actual] = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week43Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week44Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week45Actual] = [tblProjectPlanFonts].[Start], " amp; _
         "[tblProjectPlanB01].[Week46Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week47Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week48Actual] = [tblProjectPlanFonts].[Start], " amp; _
         "[tblProjectPlanB01].[Week49Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week50Actual] = [tblProjectPlanFonts].[Start], [tblProjectPlanB01].[Week51Actual] = [tblProjectPlanFonts].[Start], " amp; _
        "[tblProjectPlanB01].[Week52Actual] = [tblProjectPlanFonts].[Start] FROM [tblProjectPlanB01] INNER JOIN [tblProjectPlanFonts] ON [tblProjectPlanFonts].[Select] = '1' WHERE [tblProjectPlanB01].[StartWeekActual] <= 1  AND [tblProjectPlanB01].[StartWeekActual] <> 0"

         Application.DoCmd.RunSQL (st_sql)

Комментарии:

1. Виньеш, я пытаюсь выполнить ваше предложение выше, но у меня возникает проблема с внутренним соединением. Я не уверен, что внутреннее соединение является правильным решением, учитывая, что нет соответствующего значения поля поля. Я скорректировал приведенный выше код, чтобы отразить ваше предложение, но теперь получаю ошибку времени выполнения 3075

Ответ №2:

Вам не хватает квадратных скобок ]

Попробуйте это

 st_sql = "UPDATE R,S
          SET  R.[week1actual] = S.[start],R.[week2actual] = S.[start], 
               R.[week3actual] = S.[start],R.[week4actual] = S.[start], 
               R.[week5actual] = S.[start],R.[week6actual] = S.[start], 
               R.[week7actual] = S.[start],R.[week8actual] = S.[start], 
               R.[week9actual] = S.[start],R.[week10actual] = S.[start], 
               R.[week11actual] = S.[start],R.[week12actual] = S.[start], 
               R.[week13actual] = S.[start],R.[week14actual] = S.[start], 
               R.[week15actual] = S.[start],R.[week16actual] = S.[start], 
               R.[week17actual] = S.[start],R.[week18actual] = S.[start], 
               R.[week19actual] = S.[start],R.[week20actual] = S.[start], 
               R.[week21actual] = S.[start],R.[week22actual] = S.[start], 
               R.[week23actual] = S.[start],R.[week24actual] = S.[start], 
               R.[week25actual] = S.[start],R.[week26actual] = S.[start], 
               R.[week27actual] = S.[start],R.[week28actual] = S.[start], 
               R.[week29actual] = S.[start],R.[week30actual] = S.[start], 
               R.[week31actual] = S.[start],R.[week32actual] = S.[start], 
               R.[week33actual] = S.[start],R.[week34actual] = S.[start], 
               R.[week35actual] = S.[start],R.[week36actual] = S.[start], 
               R.[week37actual] = S.[start],R.[week38actual] = S.[start], 
               R.[week39actual] = S.[start],R.[week40actual] = S.[start], 
               R.[week41actual] = S.[start],R.[week42actual] = S.[start], 
               R.[week43actual] = S.[start],R.[week44actual] = S.[start], 
               R.[week45actual] = S.[start],R.[week46actual] = S.[start], 
               R.[week47actual] = S.[start],R.[week48actual] = S.[start], 
               R.[week49actual] = S.[start],R.[week50actual] = S.[start], 
               R.[week51actual] = S.[start],R.[week52actual] = S.[start] 
       FROM [tblProjectPlanB01] R INNER JOIN [tblProjectPlanFonts] S ON S.[Select] = '1'
       WHERE R.[StartWeekActual] <= 1 AND R.[StartWeekActual] <> 0"
Метки: