CODEWITHSUNDEEP
javamysqlsyntax
Nic
Nic

Reputation: 98

Java and MYSQL Syntax Issue

I'm trying to insert data into my MYSQL databse. I want to insert an int into the database which I have no problem doing. However, I want to INSERT INTO (VALUES) WHERE. I get a MYSQL syntax error when I try this.

I can INSERT and SELECT WHERE as long as they are in two seperate statements. Here is my code:

    String query = ("INSERT INTO `accounts` (inventory) " + "VALUES ('"
            + Inventory.inventory + "') WHERE username='" + Frame.username
            + "' and password = '" + Frame.password + "'");

Upvotes: 1

Views: 131

Answers (4)

homaynick
homaynick

Reputation: 1

The WHERE is not applicable in INSERT INTO Syntax. You want insert a new row in the table, and you should add the username and password as well as Inventory.inventory in VALUES set.

Upvotes: 0

John Woo
John Woo

Reputation: 263933

Basically, an INSERT statement can not have a WHERE clause. I am thinking that you want to UPDATE a certain record, eg

UPDATE accounts
SET inventory = 'valueHere'
WHERE userName = 'userHEre' AND password = 'passHere'

The only time an INSERT statement can have a WHERE clause is when you are inserting records from the result of a SELECT statement, eg

INSERT INTO tableName (col1, ..., colN)
SELECT col1, ..., colN
FROM table2
// WHERE ..your conditions here..

As a sidenote, your current coding style is vulnerable with SQL Injection. Consider using PreparedStatement.

Basic example of a PreparedStatement

String updateString = "UPDATE accounts SET inventory = ? WHERE userName = ? AND password = ?";
PreparedStatement updateStmt = con.prepareStatement(updateString);
updateStmt.setString(1, Inventory.inventory);
updateStmt.setString(2, Frame.username);
updateStmt.setString(3, Frame.password);
updateStmt.executeUpdate();

Upvotes: 5

Michael J. Lee
Michael J. Lee

Reputation: 12416

Not a direct answer but more of a best practice....

You should avoid doing this type of string concatenation for any sql. You vulnerable to sql injection and it does not scale well. Instead you should look at using JdbcTemplates or NamedJdbcTemplate using the opensource spring framework.

Upvotes: 0

user2336315
user2336315

Reputation: 16067

MySQL INSERT Syntax does not support the WHERE clause so that's why you have a syntax issue. Maybe you're looking for an UPDATE :

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

Upvotes: 2

Related Questions