MySql update statement in jdbc

Question

I am trying to update my database when there is a change in the inventory on the client program. The update statement ends up with the correct values in it but i get a mysql syntax error when trying to execute the update.

String sql = "UPDATE video_device SET serialNumber='" + device.getSerialNumber() + "', inventoryNumber=" + device.getInventoryNumber() + ", ownerName='" + device.getServicePerson().getName() 
                    + "', ownerTitle='" + device.getServicePerson().getJobTitle() + "', ownerEmail='" + device.getServicePerson().getEmail() + "', ownerPhoneNumber='" + device.getServicePerson().getPhoneNumber() 
                            + "', dateYear=" + device.getDateAquired().getYear() + ", dateMonth=" + device.getDateAquired().getMonth() + ", dateDay=" + device.getDateAquired().getDay() + ", typeDevice='" + device.getTypeDevice() 
                                   + "', description='" + device.getDescription() + "', price=" + device.getPrice() + ", locationBuilding='" + device.getLocation().getBuilding() + "', locationRoomNumber=" 
                                    + device.getLocation().getRoomNumber() + " WHERE serialNumber='" + orig.getSerialNumber() + "', inventoryNumber=" + orig.getInventoryNumber() + ", ownerName='" + orig.getServicePerson().getName() 
                    + "', ownerTitle='" + orig.getServicePerson().getJobTitle() + "', ownerEmail='" + orig.getServicePerson().getEmail() + "', ownerPhoneNumber='" + orig.getServicePerson().getPhoneNumber() 
                            + "', dateYear=" + orig.getDateAquired().getYear() + ", dateMonth=" + orig.getDateAquired().getMonth() + ", dateDay=" + orig.getDateAquired().getDay() + ", typeDevice='" + orig.getTypeDevice() 
                                   + "', description='" + orig.getDescription() + "', price=" + orig.getPrice() + ", locationBuilding='" + orig.getLocation().getBuilding() + "', locationRoomNumber=" 
                                    + orig.getLocation().getRoomNumber() + ";";

Edit the question and show what sql looks like after the variable substitution. — Gordon Linoff
– Gordon Linoff, Commented Apr 21, 2014 at 1:51

domdomcodecode · Accepted Answer · 2014-04-21 01:58:21Z

1

It seems like you're separating your WHERE conditions with commas. You have to use AND/OR to separate them.

What it looks like you're doing:

SELECT ...
FROM ...
WHERE a=1, b=2, c=3
etc

What you should be doing:

SELECT ...
FROM ...
WHERE a=1 AND b=2 AND C=3
ETC

answered Apr 21, 2014 at 1:58

domdomcodecode

2,4734 gold badges21 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

MySql update statement in jdbc

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related