Express: "If row exsists, remove from table 1 and insert into table 2. Return success." in mySQL

Question

I have two tables, in which I want the following logic to occur:

if (any row with a specific id exist in table1)
{
    1. Delete the row from table1
    2. insert some data into the table2 with the id as one of the values
    3. return success somehow (for me to verify in java)
}
else
{
    return fail
}

I'm sure this can be expressed in a clever manner, but I can not figure out how! Can somebody help me translate this from my procedural way of thinking?

Greetings

Olaf Dietsche · Answer 1 · 2012-10-18T08:32:44.803

0

Depending on the language (java?) you're using:

PreparedStatement stmt = conn.prepareStatement("select * from table1 where id = ?");
stmt.setInt(1, id);
ResultSet rs = stmt.executeQuery();
if (rs.first()) {
    // same for insert and delete
    insert into table2 (id, col2, col3) values(?, ?, ?);
    delete from table1 where id = ?;
    return true;
} else {
    return false;
}

edited Oct 18 '12 at 08:32

answered Oct 17 '12 at 21:50

Olaf Dietsche

66,104
6
91
177

Thanks for your answer, however - it has some shortcomings: 1. if the row in table1 does not exist, 'null' will be inserted, this is not desirable. Instead, the query must be terminated and a failure must be reported (something like false, or 0), which brings me to the next shortcoming; 2. no response is given back so there is no way to determine if the query succeeded or not. – Andak Oct 17 '12 at 23:52

score 0 · Accepted Answer · edited May 23 '17 at 11:56

After some research I found this post. I slightly modified Mike's answer and ended up with this query:

START TRANSACTION;
INSERT INTO table1(col1, col2, col3, id)
SELECT * FROM (SELECT 'value1', 'value2,'valu3', 'id') AS tmp
WHERE EXISTS (
    SELECT id FROM table2 WHERE id='123'
    ) LIMIT 1;
DELETE FROM table2 WHERE id='123';
COMMIT;

If id exists in table2, then the insert will be performed in table1 and deleted from table 2. Else, the insert will not be performed and the delete will not find any rows with the id 123 - so it will not be deleted. I also use START TRANSACTION and COMMIT to temporary disable AUTO COMMIT mode and thereby ensure that either all transactions occur, or none (in case of failure). I can then, in Java, check how many rows that where affected and see if the update where executed or not.

Express: "If row exsists, remove from table 1 and insert into table 2. Return success." in mySQL

2 Answers2