incorrect syntax near @po in Update-statement

Question

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?

score 1 · Answer 1 · answered Feb 01 '12 at 14:14

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)

score 0 · Accepted Answer · answered Jan 31 '12 at 20:28

0

I don't believe you can specify the column name using a parameter like this - that part needs to be part of the SQL itself. Only values can be parameterized.

answered Jan 31 '12 at 20:28

Jon Skeet

1,421,763
867
9,128
9,194

I used to execute updates of the form 'UPDATE table SET ? = ? WHERE ? = ? a lot, so it should be possible I think – Freek8 Jan 31 '12 at 20:31
@Freek8: Really? With what database system, and what language? – Jon Skeet Jan 31 '12 at 20:34
@Freek8: Hmm... that seems very odd to me. I've certainly seen it fail on other systems when you try to use it for column or table names instead of values. – Jon Skeet Jan 31 '12 at 20:37
I have been using it in the past week a lot, but I have been getting odd results too (no errors though). According to [this](http://stackoverflow.com/questions/3135973/variable-column-names-using-prepared-statements) question it is indeed not possible. I think I will have to use multiple preparedstatements then.. – Freek8 Jan 31 '12 at 20:45

incorrect syntax near @po in Update-statement

2 Answers2