Update 语句中 @po 附近的语法不正确

发布于 2025-01-01 12:29:42 字数 827 浏览 5 评论 0原文

我使用 Java 中的 preparedStatement 来更新和替换 MSSQL 2008 数据库中的记录，如下所示：

ps = settings.conn.prepareStatement("UPDATE table SET ? = replace((?), (?), (?)) ");

然后我按如下方式传递参数：

String[] columns= {"a", "b", "c", "d"};
for (int i = 0; i < columns.length; i++) {
  ps.setString(1, columns[i]);
  ps.setString(2, columns[i]);
  ps.setString(3, " " + oldName.trim() + " ");
  ps.setString(4, " " + newName.trim() + " ");
  ps.addBatch();

  batchSize++;
  if (batchSize > 5000) {
    batchSize = 0;
    ps.executeBatch();
  }
}

我收到很多错误消息，指出@po 附近的语法不正确。从这个问题我知道 Top-statement 应该包含在在参数化语句中使用时用括号括起来。

难道 Update 语句还需要一些额外的格式才能使用它吗？或者还有其他问题吗？

原文

I'm using a preparedStatement in Java to update and replace records in a MSSQL 2008 database as follows:

ps = settings.conn.prepareStatement("UPDATE table SET ? = replace((?), (?), (?)) ");

I am then passing in the arguments as follows:

String[] columns= {"a", "b", "c", "d"};
for (int i = 0; i < columns.length; i++) {
  ps.setString(1, columns[i]);
  ps.setString(2, columns[i]);
  ps.setString(3, " " + oldName.trim() + " ");
  ps.setString(4, " " + newName.trim() + " ");
  ps.addBatch();

  batchSize++;
  if (batchSize > 5000) {
    batchSize = 0;
    ps.executeBatch();
  }
}

I get a lot of error messages saying incorrect syntax near @po. From this question I understand that the Top-statement should be inclosed in brackets when it is used in a parameterized statement.

Could it be that the Update statement also needs some additional formatting before I can use it? Or is something else going wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

感情旳空白 2025-01-08 12:29:42

如前所述，列名不是绑定参数。语句中的动态列名更接近于动态sql。如果语句的结构是动态的，则sql server无法真正解析和准备语句。

看起来这个例子试图实现这样的目标：

UPDATE table SET a = replace((a), (?), (?))
                ,b = replace((b), (?), (?))
                ,c = replace((c), (?), (?))
                ,d = replace((d), (?), (?))

如果目的不是更新所有列，而只是更新其中的一些列，则可以执行如下操作：

UPDATE table SET a = NVL2(?, replace((a), (?), (?)), a)
                ,b = NVL2(?, replace((b), (?), (?)), b)
                ,c = NVL2(?, replace((c), (?), (?)), c)
                ,d = NVL2(?, replace((d), (?), (?)), d)

如果列的输入参数为 NULL，则设置列值等于当前值（即没有真正修改），否则将列值设置为计算结果。（Oracle 语法）

As mentioned earlier, a column name is not a bind parameter. A dynamic column name in the statement is closer to dynamic sql. The sql server can't really parse and prepare the statement if the structure of the statement is dynamic.

Looks like the example is trying to achieve something like this:

UPDATE table SET a = replace((a), (?), (?))
                ,b = replace((b), (?), (?))
                ,c = replace((c), (?), (?))
                ,d = replace((d), (?), (?))

If the intention was not to update all columns, but only some of them, you could do something like this:

UPDATE table SET a = NVL2(?, replace((a), (?), (?)), a)
                ,b = NVL2(?, replace((b), (?), (?)), b)
                ,c = NVL2(?, replace((c), (?), (?)), c)
                ,d = NVL2(?, replace((d), (?), (?)), d)

If the input parameter for a column is NULL, then set the column value equal to the same as what it currently is (ie don't really modified), otherwise set the column value to calculated result. (Oracle syntax)

回复收藏 0 原文