0
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?

3
Contributors
15
Replies
16
Views
6 Years
Discussion Span
Last Post by smantscheff
Featured Replies
  • 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: [CODE]INSERT IGNORE INTO user values('x'); INSERT IGNORE INTO item values(1); INSERT IGNORE INTO tableA values('x',1,'z'); … Read More

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

0

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 = ?
0

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.

0

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

Edited by smantscheff: I wrote nonsense.

0
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;
0

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

0
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?

1

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.

0

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();
1

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.