CODEWITHSUNDEEP
javamysqljdbc
aime
aime

Reputation: 127

INSERT INTO ... VALUES ... SELECT. How to make nested statement with INSERT?

Please give me an idea how to execute statement to DB. There are 2 tables(sorry for incorrect writing - it is just to give information about columns):

  1. contractors(contractor_id, contractor_name, contract_num)

  2. invoices(contractor_id, invoice_num, invoice_date, invoice_amount)

The 'contractor_id' in 'invoices' is a foreign key for 'contractor_id' in 'contractors'. How to insert in 'invoices'-table the following data: invoice_num, invoice_date, invoice_amount, if I have the 'contractor_name' from 'contractors'-table?

The solution which works is:

public void insertInvoiceDataByContractorName(String contractorsName, String invoiceNum, String date, float amount) {
        String contrId = null;
        try {
            preparedStatement = connection.prepareStatement("SELECT contractor_id FROM contractors WHERE contractor_name=?");
            preparedStatement.setString(1, contractorsName);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                contrId = resultSet.getString(1);
            }
            preparedStatement = connection.prepareStatement("INSERT INTO invoices VALUES(?, ?, ? ,?)");
            preparedStatement.setString(1, contrId);
            preparedStatement.setString(2, invoiceNum);
            preparedStatement.setString(3, date);
            preparedStatement.setFloat(4, amount);
            preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (preparedStatement != null) {preparedStatement.close();}
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

I don't know how to make it correct and more simpler by using just one statement. Thank you in advance for pieces of advice.

Upvotes: 0

Views: 751

Answers (1)

6ton
6ton

Reputation: 4214

Use 

INSERT INTO invoices SELECT contractor_id, ?, ?, ?  FROM contractors WHERE contractor_name=?

this will insert 1 row assuming your contractor name is unique

Upvotes: 3

Related Questions