CODEWITHSUNDEEP
javasqljsp
rdk1992
rdk1992

Reputation: 406

Update statement syntax error

I have the following string which holds the query I want to execute:

     query = "UPDATE inventario"
     + " set descripcion = '" + descripcion + "',"
     + " set bodega = '" + bodega + "'"
     + " where codigo = " + codigo;

I get an Update statement syntax error but I dont see where is the error. Any help is appreciated. columns "descripcion" and "bodega" are text type columns.

Upvotes: 2

Views: 1090

Answers (3)

John Woo
John Woo

Reputation: 263723

Remove extra SET on your query.

query = "UPDATE inventario"
 + " set descripcion = '" + descripcion + "',"
 + "     bodega = '" + bodega + "'"
 + " where codigo = " + codigo;

but that query is vulnerable with SQL Injection. Please parameterize your query. Example,

String query = "UPDATE inventario" + 
               "   set descripcion = ?, bodega = ? " + 
               " where codigo = ?";
PreparedStatement prep = connection.prepareStatement(query);
prep.setString(1, descripcion);
prep.setString(2, bodega);
prep.setInt(3, codigo);
prep.executeUpdate();

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1500675

Well it's probably because you've got multiple set parts instead of using comma separation, and potentially because you don't have quotes around the codigo value (if that's another string)... but I'd strongly advise you not to create SQL like this anyway, with values directly in the SQL.

Instead, use a prepared statement:

String sql = "UPDATE inventario set descripcion=?, bodega=? where codigo=?";
PreparedStatement st = conn.prepareStatement(sql);
st.setString(1, descripcion);
st.setString(2, bodega);
st.setString(3, codigo);

Using prepared statements has three immediate benefits:

  • It avoids SQL injection attacks (think about what happens if your description has a quote in it)
  • It separates code (SQL) from data (the values)
  • It means you avoid conversions for types like datetime, where going via a string representation is a huge potential source of error

Upvotes: 8

Vikdor
Vikdor

Reputation: 24124

SET keyword is needed only once. Multiple columns that are being updated should be separated by commas, as in the below statement.

query = "UPDATE inventario"
    + " set descripcion = '" + descripcion + "',"
    + "     bodega = '" + bodega + "'"
    + " where codigo = " + codigo;

BTW, it is highly recommended to use PreparedStatement for such operations instead of forming the query like this to avoid SQL Injection attacks.

query = "UPDATE inventario"
    + " set descripcion = ?, bodega = ? "
    + " where codigo = ?";
PreparedStatement ps = connection.prepareStatement(query);
ps.setString(1, descripcion);
ps.setString(2, bodega);
ps.setInt(3, codigo);
int updateCount = ps.executeUpdate();

Upvotes: 1

Related Questions