CODEWITHSUNDEEP
javamysqlsqlsyntax
OiRc
OiRc

Reputation: 1622

Update syntax error

Is it possible to insert a from field in a update query? i need it because before update something,i need to do some controls. p.s i know that i have to use prepared statement but i want to know this error,i suppose it is a missing quote,that i can't see. the error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from warehouse,product where warehouse.idProduct=shop.idProduct and warehouse.id' at line 1

int rs = st.executeUpdate("UPDATE shop SET quantity='"
                        + Quantity
                        + "' from warehouse,product where  
                         warehouse.idProduct=shop.idProduct and 
                         warehouse.idProduct=product.id and product.brand='"
                        + Brand + "' and product.productType='" + Product
                        + "'");

Upvotes: 0

Views: 55

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I think you want an update with a join:

UPDATE shop s join
       warehouse w
       on w.idProduct = s.idProduct join
       product p
       on w.idProduct = p.id and
          p.productType = '"+Product+"' and
          p.brand = '" + Brand + "'
    SET s.quantity = "+ Quantity

As a note. I'm guess that Quantity is numeric. If so, you don't need quotes around the value.

Upvotes: 0

John Conde
John Conde

Reputation: 219814

That just isn't valid SQL. UPDATE statements cannot have a FROM clause.

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

Upvotes: 1

Related Questions