Update SQL statement with multiple fields within ONE table

Question

I am trying to write an update SQL statement for many columns within one table only. For example product table. Within product table, there are many columns like name, description, price, quantity, image, category, status.

So I came out with this SQL statement:

String sql = "UPDATE sm_product SET productDescription = '" + desc +
    "' , productPrice = ' + price + ', productQuantity = ' + quantity +
    ', productImage = '" + image + "', productCategory = '" + category +
    '"  WHERE productName = '" + name + "'";

However, the compiler told me that there are unclosed character literal and not a statement. I wonder how should I fix this SQL statement because I only have one table to update. But within that table, there are many fields.

Thanks in advance.

Note the edit I made, and how the syntax highlighting is showing that many of your to-be-inserted varabieslare NOT being highlighted properly. — Marc B
– Marc B, Commented Jun 21, 2013 at 15:34
unrelated to your question, but still something to note, make sure you are sanitizing your inputs so that you don't get SQL Injection. — Ryan Drost
– Ryan Drost, Commented Jun 21, 2013 at 15:36
Please tag your questions with the language you're using, so we can give answers appropriate for your environment. — Barmar
– Barmar, Commented Jun 21, 2013 at 15:57

Tom · Accepted Answer · 2013-06-21 15:36:17Z

1

It looks like you have problems with your quotes. Try this:

String sql = "UPDATE sm_product SET productDescription = '" + desc +
    "' , productPrice = " + price + ", productQuantity = " + quantity +
    ", productImage = '" + image + "', productCategory = '" + category +
    "'  WHERE productName = '" + name + "'";

This is assuming that price and quantity are numeric and the rest are strings.

answered Jun 21, 2013 at 15:36

Tom

6,6633 gold badges24 silver badges42 bronze badges

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

2 Comments

user2424370 Over a year ago

So numerics are "". I thought they were ''. But how come when I write in one line, there's an error?

Barmar Over a year ago

Numbers don't need any quotes, only strings need to be quoted.

Collectives™ on Stack Overflow

Update SQL statement with multiple fields within ONE table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related