如何在 INSERT INTO 中添加主键和外键。

huangapple go评论66阅读模式
英文:

How to add to INSERT INTO PRIMARY KEY AND FOREIGN KEY

问题

String sql = "INSERT INTO locations(locations_id, username, password, id, type_of_id, first_name, last_name, phone, email, date_of_birth, address, sex) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"
    + "; INSERT INTO employees(employees_id, locations_id) SELECT ?, locations.locations_id FROM locations INNER JOIN employees ON locations.locations_id = employees.locations_id";

try {
    MicroModelGUI micro = new MicroModelGUI();
    PreparedStatement consulta = micro.connection.prepareStatement(sql);
    consulta.setString(1, tflid.getText());
    consulta.setString(2, tfuser.getText());
    consulta.setString(3, tfpass.getText());
    consulta.setString(4, tfid.getText());
    consulta.setString(5, tftoid.getText());
    consulta.setString(6, tffirst.getText());
    consulta.setString(7, tflast.getText());
    consulta.setString(8, tfphone.getText());
    consulta.setString(9, tfemail.getText());
    consulta.setString(10, tffdn.getText());
    consulta.setString(11, tfaddress.getText());
    consulta.setString(12, tfsex.getText());
    consulta.setString(13, tfeid.getText());
    int resultado = consulta.executeUpdate();
英文:

So i have two tables: locations and employees i want locations_id to be the same in employees.locations_id, I am trying to make it all in one statement
the erros is this You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO employees(employees_id, locations_id) VALUES('e1598','')' at line 1

String sql = " INSERT INTO locations( locations_id , username, password, id, type_of_id, first_name, last_name, phone, email, date_of_birth, address, sex ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"
 **Error here --->**           + "INSERT INTO employees(employees_id,locations_id) VALUES (?,SELECT locations_id FROM locations INNER JOIN employees ON locations.locations_id =employees.locations_id)";
    try {

       MicroModelGUI micro = new MicroModelGUI();
        PreparedStatement consulta =  micro.connection.prepareStatement(sql);
         consulta.setString(1, tflid.getText());
         consulta.setString(2, tfuser.getText());
         consulta.setString(3, tfpass.getText());
         consulta.setString(4, tfid.getText());
         consulta.setString(5, tftoid.getText());
         consulta.setString(6, tffirst.getText());
         consulta.setString(7,tflast.getText());
         consulta.setString(8,tfphone.getText());
         consulta.setString(9,tfemail.getText());
         consulta.setString(10,tffdn.getText());
         consulta.setString(11,tfaddress.getText());
         consulta.setString(12,tfsex.getText());
         consulta.setString(13,tfeid.getText());
         int resultado = consulta.executeUpdate();

答案1

得分: 1

你应该在这里使用INSERT INTO ... SELECT语句:

INSERT INTO employees (employees_id, locations_id)
SELECT ?, l.locations_id
FROM locations l
INNER JOIN employees e ON l.locations_id = e.locations_id;

?占位符处,您将从您的Java代码中绑定一个值。请注意,尽管您所使用的SQL版本可能支持将标量子查询放入VALUES子句中,但很可能您的确切版本会引发错误,因为它会返回多行。

英文:

You should be using an INSERT INTO ... SELECT here:

INSERT INTO employees (employees_id, locations_id)
SELECT ?, l.locations_id
FROM locations l
INNER JOIN employees e ON l.locations_id = e.locations_id;

To the ? placeholder you would bind a value from your Java code. Note that while your version of SQL might support putting a scalar subquery into a VALUES clause, it is likely that your exact version would cause an error, as it would return more than one row.

huangapple
  • 本文由 发表于 2020年10月13日 23:39:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/64338503.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定