PreparedStatement and subquery

[Ignatius88]

pstmt = conn.prepareStatement("INSERT INTO tableA (userid, itemid) VALUES (?, ?) ON DUPLICATE KEY UPDATE value = ?");
pstmt.setString(1, userid);
pstmt.setString(2, itemid);
pstmt.setString(3, value);
pstmt.executeUpdate();

This code works

pstmt = conn.prepareStatement("INSERT INTO tableA (userid, itemid) VALUES ((SELECT userid FROM user WHERE userid = ?), ?) ON DUPLICATE KEY UPDATE value = ?");
pstmt.setString(1, userid);
pstmt.setString(2, itemid);
pstmt.setString(3, value);
pstmt.executeUpdate();

This code does not work. What is wrong here? Is there a way that can make the second query run?

[griswolf]

Cut and paste the error please. Or do you mean it doesn't work as you expected?

[Ignatius88]

Cut and paste the error please. Or do you mean it doesn't work as you expected?

It does not return any error. It just does not insert. Doing this works...

INSERT INTO tableA (userid, itemid) SELECT (SELECT userid FROM user WHERE userid = ?), ? FROM DUAL ON DUPLICATE KEY UPDATE value = ?

[smantscheff]

Run your prepared statement from the mysql command line which will tell you what went wrong.

[Ignatius88]

What I intended to do was on insert, it finds a row of which has same userid and itemid. If there is an existing row, then I simply update value.

it does not insert value for the first run, but if I execute it one more time, it does. Is there a way to do that? userid and itemid are foreign keys.

[smantscheff]

Please show the CREATE TABLE statement. IS (userid, itemid) a unique key?

[Ignatius88]

CREATE TABLE `user` (
      userid varchar(30) not null,
      PRIMARY KEY (userid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `item` (
      itemid int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE tableA (
      userid varchar(30) not null,
      itemid int not null,
      PRIMARY KEY (userid, itemid),
      KEY userid (userid),
      KEY itemid (itemid)
      CONSTRAINT userid FOREIGN KEY (userid) REFERENCES user (userid) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT itemid FOREIGN KEY (itemid) REFERENCES item (itemid) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[Ignatius88]

itemid in item table is also a primary key. My mistake.

[smantscheff]

This does not make sense. Which field do you want to update on duplicate keys? There is no field named "value" in your table.

[Ignatius88]

CREATE TABLE `user` (
      userid varchar(30) not null,
      PRIMARY KEY (userid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `item` (
      itemid int not null,
      PRIMARY KEY (itemid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE tableA (
      userid varchar(30) not null,
      itemid int not null,
      value int,
      PRIMARY KEY (userid, itemid),
      KEY userid (userid),
      KEY itemid (itemid)
      CONSTRAINT userid FOREIGN KEY (userid) REFERENCES user (userid) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT itemid FOREIGN KEY (itemid) REFERENCES item (itemid) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Sorry I missed few thing. Maybe my sql statement is wrong. If it is wrong, how do I insert value if a row with userid and itemid does not exist? And if such row exist I update value?

[smantscheff]

You could use INSERT IGNORE.
First INSERT IGNORE into your user table, then into the item table, then in tableA. Then update tableA. You can be sure that the correct data are in it afterwards:

INSERT IGNORE INTO user values('x');
INSERT IGNORE INTO item values(1);
INSERT IGNORE INTO tableA values('x',1,'z');
UPDATE tableA set value='z' where userid='x' and itemid=1;

This is a bit redundant on the query side, but easy to code and to maintain.

[Ignatius88]

So I run the all queries?

[smantscheff]

Yes.

[Ignatius88]

How do I execute multiple queries in PreparedStatement?

pstmt = conn.prepareStatement("INSERT IGNORE INTO user (user_id) values(?), INSERT IGNORE INTO item (item_id) values(?), UPDATE tableA set value = ? where user_id=? and item_id=?"); 
pstmt.setString(1, user_id);
pstmt.setString(2, item_id);
pstmt.setString(3, value);
pstmt.setString(4, user_id);
pstmt.setString(5, item_id);
pstmt.executeUpdate();

[Ignatius88]

This gives me an error that says MySQL syntax is wrong.

[smantscheff]

These are four different queries. If you use prepared statements, each needs its own statement.