MySQL INSERT IF NOT EXIST another DELETE row

Question

I have a table that joins two other tables together.

I have a list of checkboxes where, when checked, it sends the value to the server via ajax.

What I want to do is if the values ​​are not in the database, insert them (marked) or they are there, delete them (not marked)

Is there a way to do this without writing a few queries? I know this is not difficult with insert / update, but what about delete?

Answer 1

You can always delete and if the affected lines are 0, then paste. Easy, simple and works.

Answer 2

INSERT IF NOT EXISTING ... not available in MySQL (it is specific to T-SQL afaik). Use a transaction instead, but keep in mind that not every MySQL engine supports transactions, although there are no errors when using transactional statements.

Answer 3

This may help you depending on your key structure http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

Otherwise, will you be able to handle what you are doing with the subquery?

Answer 4

Not 100% of what you are looking for, but the insert does not exist: http://remy.supertext.ch/2010/11/mysqlupdate-and-insert-if-not-exists/

INSERT INTO wp_postmeta (post_id, meta_key) SELECT ?id, 'page_title' FROM DUAL WHERE NOT EXISTS ( SELECT meta_id FROM wp_postmeta WHERE post_id = ?id AND meta_key = 'page_title'); UPDATE wp_postmeta SET meta_value = ?page_title WHERE post_id = ?id AND meta_key = 'page_title'; 

Answer 5

There is no such thing as ON DUPLICATE DELETE or the proper use of IF ELSE with subqueries to do this in a single query (which would be convenient since this single query would be either successful or unsuccessful). Think that you will need to fulfill several requests to achieve your goal, just like me. But keep track of whether subsequent requests continue.

Start with the return value ("state" in this case), which is "Unknown" or "NoChange". If this returns you an ajax call, you know that the flag should return to its previous state before clicking and maybe a pop-up message (which you can carry along with json).

run your queries in the correct if / then structure and keep track of subsequent queries and update the “state” when the series has succeeded accordingly. This should do exactly what you need, to prevent the presence of black holes.

(I use some wrappers to execute queries that return true / false / null / errorinformation - which allows you to check every query result)

I assume you already have your ajax call. The code example starts with your ajax call starting processing from the server side:

 ob_end_clean(); $json = array("state" => "Unknown"); $module_id = (isset($_POST['module_id']) ? $_POST['module_id'] : null); $group_id = (isset($_POST['group_id']) ? $_POST['group_id'] : null); $action = (isset($_POST['action']) ? $_POST['action'] : null); if (!empty($module_id) && !empty($group_id) && !empty($action)) { $query = " SELECT 1 FROM config_module_actions_groups WHERE 1 AND module_id = '{$module_id}' AND group_id = '{$group_id}' AND action = '{$action}' "; if (getQueryAsArraySingleValues($query)) { $query = " DELETE FROM config_module_actions_groups WHERE 1 AND module_id = '{$module_id}' AND group_id = '{$group_id}' AND action = '{$action}' "; if (executeQuery($query)) { $json["state"] = "Off"; } else { $json["message"] = "Not correctly deactivated"; } } else { $query = " REPLACE INTO config_module_actions_groups SET module_id = '{$module_id}', group_id = '{$group_id}', action = '{$action}' "; if (executeQuery($query)) { $json["state"] = "On"; } else { $json["message"] = "Not correctly activated"; } } } // output echo json_encode($json); exit;