Mysql: How to test an SQL Update statement before running it?

In some cases, running an UPDATE statement in production can save the day. However a borked update can be worse than the initial problem.

Short of using a test database, what are options to tell what an update statement will do before running it?

 Answers

In addition to using a transaction as Imad has said (which should be mandatory anyway) you can also do a sanity check which rows are affected by running a select using the same WHERE clause as the UPDATE.

So if you UPDATE is

UPDATE foo
  SET bar = 42
WHERE col1 = 1
  AND col2 = 'foobar';

The following will show you which rows will be updated:

SELECT *
FROM foo
WHERE col1 = 1
  AND col2 = 'foobar';

---

What about Transactions? They have the ROLLBACK-Feature.

@see https://dev.mysql.com/doc/refman/5.0/en/commit.html

For example:

START TRANSACTION;
SELECT * FROM nicetable WHERE somthing=1;
UPDATE nicetable SET nicefield='VALUE' WHERE somthing=1;
SELECT * FROM nicetable WHERE somthing=1; #check

COMMIT;
# or if you want to reset changes 
ROLLBACK;

SELECT * FROM nicetable WHERE somthing=1; #should be the old value

In general these lines will not be executed as once. In PHP f.e. you would write something like that (perhaps a little bit cleaner, but wanted to answer quick ;-) ):

$MysqlConnection->query('START TRANSACTION;');
$erg = $MysqlConnection->query('UPDATE MyGuests SET lastname='Doe' WHERE id=2;');
if($erg)
    $MysqlConnection->query('COMMIT;');
else
    $MysqlConnection->query('ROLLBACK;');

Another way would be to use MySQL Variables (see https://dev.mysql.com/doc/refman/5.7/en/user-variables.html and https://.com/a/18499823/1416909 ):

# do some stuff that should be conditionally rollbacked later on

SET @v1 := UPDATE MyGuests SET lastname='Doe' WHERE id=2;
IF(v1 < 1) THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

But I would suggest to use the language wrappers available in your favorite programming language.

---

Not a direct answer, but I've seen many borked prod data situations that could have been avoided by typing the WHERE clause first! Sometimes a WHERE 1 = 0 can help with putting a working statement together safely too. And looking at an estimated execution plan, which will estimate rows affected, can be useful. Beyond that, in a transaction that you roll back as others have said.

---

make a SELECT of it,

like if you got

UPDATE users SET id=0 WHERE name='jan'

convert it to

SELECT * FROM users WHERE name='jan'

Mysql: Foreign key constraints: When to use ON UPATE and ON DELETE

I'm designing my database schema using MySQL Workbench, which is pretty cool because you can do diagrams and it converts them :P

Anyways, I've decided to use InnoDB because of it's Foreign Key support. One thing I noticed though is that it allows you to set On Update and on Delete options for foreign keys. Can someone explain where "Restrict", "Cascade" and set null could be used in a simple example?

For example, say I have a user table which includes a userID. And say I have a message table message which is a many-to-many which has 2 foreign keys (which reference the same primary key, userID in the user table). Is setting the On Update and On Delete options any useful in this case? If so, which one do I choose? If this isn't a good example, could you please come up with a good example to illustrate how these could be useful?

Answers

Do not hesitate to put constraints on the database. You'll be sure to have a consistent database, and that's one of the good reasons to use a database. Especially if you have several applications requesting it (or just one application but with a direct mode and a batch mode using different sources).

With MySQL you do not have advanced constraints like you would have in postgreSQL but at least the foreign key constraints are quite advanced.

We'll take an example, a company table with a user table containing people from theses company

CREATE TABLE COMPANY (
     company_id INT NOT NULL,
     company_name VARCHAR(50),
     PRIMARY KEY (company_id)
) ENGINE=INNODB;

CREATE TABLE USER (
     user_id INT, 
     user_name VARCHAR(50), 
     company_id INT,
     INDEX company_id_idx (company_id),
     FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;

Let's look at the ON UPDATE clause:

• ON UPDATE RESTRICT : the default : if you try to update a company_id in table COMPANY the engine will reject the operation if one USER at least links on this company.
• ON UPDATE NO ACTION : same as RESTRICT.
• ON UPDATE CASCADE : the best one usually : if you update a company_id in a row of table COMPANY the engine will update it accordingly on all USER rows referencing this COMPANY (but no triggers activated on USER table, warning). The engine will track the changes for you, it's good.
• ON UPDATE SET NULL : if you update a company_id in a row of table COMPANY the engine will set related USERs company_id to NULL (should be available in USER company_id field). I cannot see any interesting thing to do with that on an update, but I may be wrong.

And now on the ON DELETE side:

• ON DELETE RESTRICT : the default : if you try to delete a company_id Id in table COMPANY the engine will reject the operation if one USER at least links on this company, can save your life.
• ON DELETE NO ACTION : same as RESTRICT
• ON DELETE CASCADE : dangerous : if you delete a company row in table COMPANY the engine will delete as well the related USERs. This is dangerous but can be used to make automatic cleanups on secondary tables (so it can be something you want, but quite certainly not for a COMPANY<->USER example)
• ON DELETE SET NULL : handful : if you delete a COMPANY row the related USERs will automatically have the relationship to NULL. If Null is your value for users with no company this can be a good behavior, for example maybe you need to keep the users in your application, as authors of some content, but removing the company is not a problem for you.

usually my default is: ON DELETE RESTRICT ON UPDATE CASCADE. with some ON DELETE CASCADE for track tables (logs--not all logs--, things like that) and ON DELETE SET NULL when the master table is a 'simple attribute' for the table containing the foreign key, like a JOB table for the USER table.

Edit

It's been a long time since I wrote that. Now I think I should add one important warning. MySQL has one big documented limitation with cascades. Cascades are not firing triggers. So if you were over confident enough in that engine to use triggers you should avoid cascades constraints.

• http://dev.mysql.com/doc/refman/5.6/en/triggers.html

MySQL triggers activate only for changes made to tables by SQL statements. They do not activate for changes in views, nor by changes to tables made by APIs that do not transmit SQL statements to the MySQL Server

• http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

==> See below the last edit, things are moving on this domain

Triggers are not activated by foreign key actions.

And I do not think this will get fixed one day. Foreign key constraints are managed by the InnoDb storage and Triggers are managed by the MySQL SQL engine. Both are separated. Innodb is the only storage with constraint management, maybe they'll add triggers directly in the storage engine one day, maybe not.

But I have my own opinion on which element you should choose between the poor trigger implementation and the very useful foreign keys constraints support. And once you'll get used to database consistency you'll love PostgreSQL.

12/2017-Updating this Edit about MySQL:

as stated by @IstiaqueAhmed in the comments, the situation has changed on this subject. So follow the link and check the real up-to-date situation (which may change again in the future).

---

Addition to @MarkR answer - one thing to note would be that many PHP frameworks with ORMs would not recognize or use advanced DB setup (foreign keys, cascading delete, unique constraints), and this may result in unexpected behaviour.

For example if you delete a record using ORM, and your DELETE CASCADE will delete records in related tables, ORM's attempt to delete these related records (often automatic) will result in error.

Updating multiple values Mysql

How can i update multiple values in mysql , this didn't work

 UPDATE test SET list=0,
price= 0.00 cprice= 0.00 WHERE test.id =3232

 Answers

you need to put a comma between the two different values. For example:

UPDATE orders 
   SET listPrice = 0
     , bloggerPrice = 0.00
     , customerPrice = 0.00 
WHERE orders.id =245745

---

Try:

UPDATE orders 
SET listprice=0, bloggerPrice=0.00, customerPrice=0.00 
WHERE id=245745

Insert into a MySQL table or update if exists

I want to add a row to a database table, but if a row exists with the same unique key I want to update the row.

For example,

insert into table (id, name, age) values(1, "A", 19)

Let’s say the unique key is id, and in my database there is a row with id = 1. In that case I want to update that row with these values. Normally this gives an error. If I use insert IGNORE it will ignore the error, but it still won’t update.

 Answers

Use INSERT ... ON DUPLICATE KEY UPDATE

QUERY:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19

---

Try this out:

INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;

Hope this helps.

---

When using batch insert use the following syntax:

INSERT INTO TABLE (id, name, age) VALUES (1, "A", 19), (2, "B", 17), (3, "C", 22)
ON DUPLICATE KEY UPDATE
    name = VALUES (name),
    ...

---

When using SQLite:

REPLACE into table (id, name, age) values(1, "A", 19)

Provided that id is the primary key. Or else it just inserts another row. 

---

INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;

Also do not forget to concern about the unique key constraint.

ALTER TABLE `table` ADD UNIQUE `unique_key` ( `id` ) 

MySQL UPDATE with SUBQUERY of same table

I want to update a table named "test" with some sub-query where sub-query is the same "test" table. I wrote the below SQL first to update my "test" table:

UPDATE test SET roll=CONCAT(roll,"-",id) WHERE id IN (SELECT id FROM test WHERE id%2=0)

And got below error:

#1093 - Table 'test' is specified twice, both as a target for 'UPDATE' and as a separate source for data.

And in this case we have a nice solution provided by MySQL itself is processing temporary table, query is below:

UPDATE test SET roll=CONCAT(roll,"-",id) WHERE id IN (SELECT t.id FROM (SELECT * FROM test) AS t WHERE t.id%2=0)

And it works. Marked yellow color section actually do the temporary table works.

MySQL Update Table Using Join Of Other Tables | MySQL Join Table On Update

UPDATE table1 AS t1 LEFT JOIN table2 AS t2 ON (t1.t2_id = t2.id)
SET t1.some_field = t1.some_field + 1 
WHERE t1.id in (1,2,3) AND t2.some_field = "SOME MATCH VALUE"

Cross Table Update with MySQL

Using MySQL version 4.0 or higher you can update a table by joining two or more tables together; note that the examples shown in this article are not possible with MySQL 3.23 or earlier. By joining two tables together you can update one table based on fields in associated records in another table.

Let's say for example you have a product table which stores information about products and a productPrice table which has pricing information and you want to update the prices based on when the product was created (in the examples below you want to discount all your older stuff to 80% of the current price).

In MySQL you can do this in one of two ways. The first is do do a join using commas, like so:

UPDATE product p, productPrice pp
SET pp.price = pp.price * 0.8
WHERE p.productId = pp.productId
AND p.dateCreated < '2004-01-01'

The second way is to use inner join syntax as shown below. This syntax is slightly more flexible as it means you can use left and right joins as well as inner joins, whereas the previous example is an implicit inner join.

UPDATE product p
INNER JOIN productPrice pp
ON p.productId = pp.productId
SET pp.price = pp.price * 0.8
WHERE p.dateCreated < '2004-01-01'

Another example of doing this in MySQL but using a left join to flag all the products that do not have associated entries in the productPrice table is as follows:

UPDATE product p
LEFT JOIN productPrice pp
ON p.productId = pp.productId
SET p.deleted = 1
WHERE pp.productId IS null

This isn't necessarily a very realistic example as you would be more likely to want to straight out delete the non-matching entries or use a select query instead to display them in a list for editing but it illustrates how it can be done. The article about how to delete records with MySQL using a cross join uses these examples to delete instead of update.

Note that it's also possible in MySQL to update values in all the tables that are joined in the query. In this example we're doing the same query as in the first two examples but also updating the "dateUpdated" value in the product table.

UPDATE product p
INNER JOIN productPrice pp
ON p.productId = pp.productId
SET pp.price = pp.price * 0.8,
p.dateUpdate = CURDATE()
WHERE p.dateCreated < '2004-01-01'

(CURDATE() is the MySQL function to return the current system date. For other date and time functions in MySQL refer to the MySQL date and time functions manual page).

The MySQL update query does not work

I have mysql table with following fields

id (which auto increments)

title,name,age,institution,iod

the fields like title,name,age,institution are entered in the form structure (html page) and by clicking submit button are stored in DB(mysql)

iod is the field is something like this 123/id (nothing but same id field but with '123/' prefix before)

now when the user enters title,name,age,institution these need to stored in DB including id and iod.

Now for executing the above i wrote the following the fields title,name,age,institution and id are successfully stored but iod is not stored.

Can you please show me the error i made here??

I wrote the following syntax for the code(part of the same code)

if ($_POST['title'] != '')
{
$value = $_POST['title'];
$value1 = $_POST['name'];
$value2 = $_POST['age'];
$value5 = $_POST['institution']; 

$sql = "INSERT INTO parentid(title,name,age,institution) VALUES('$value','$value1','$value2','$value5')"  ;

}
$sql1="SELECT * FROM parentid ORDER BY id DESC LIMIT 1";
$value6=$row['id'];
$value4= '123/' . $row['id'] ;
$sql2="UPDATE parentid SET iod='$value4' WHERE id = '$value6' ";

---

The problem is that you are not executing your queries, you just put the query in a string but then you don´t do anything with it.

For the second query (simple sample code):

$sql1="SELECT * FROM parentid ORDER BY id DESC LIMIT 1";
$result = mysql_query($sql1);
if ($row = mysql_fetch_assoc($result))
{
  $value6=$row['id'];
  // etc., error handling not added
}