Update syntax error

Question

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
                        + "'");

If you know you ought to use a prepared statement, why don't you do so? In the prepared statement, you won't have all those quotes anyway so the problem may just go away. Mind you, the code you've given us isn't valid Java... — Jon Skeet
– Jon Skeet, Commented Jan 25, 2014 at 14:54
I thought I could put in a update from a query, I was wrong, anyway I realized that prepared statements are easier to write and more protected — OiRc
– OiRc, Commented Jan 25, 2014 at 17:16

John Conde · Accepted Answer · 2014-01-25 14:55:08Z

1

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]

answered Jan 25, 2014 at 14:55

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OiRc Over a year ago

if i want to check something before update,how can i do?

John Conde Over a year ago

Do a separate SELECT statement and then do your UPDATE based on the results.

Gordon Linoff · Accepted Answer · 2014-01-25 15:09:11Z

0

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.

edited Jan 25, 2014 at 15:09

answered Jan 25, 2014 at 15:00

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Update syntax error

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related