How to handle NULLs when updating one table from another using MySQL
I have the following code, but it updates all the rows in
table_1, even when the condition is not true (i.e. where
table_1.data_1 is not present at all in
table_2.data_1). How can I adjust the following update statement so that only the values in
table_1 that match the values in
table_2 are updated?
I am trying to set all the values in
table_1.data_2 to NULL where the
data_1 value is the same in both tables.
UPDATE table_1.data_1, table_2.data_1 SET table_1.data_2 = NULL WHERE table_1.data_1 = table_2.data_1
Here is an update to my original post.
UPDATE table_1.data_2 SET table_1.data_2 = NULL WHERE table_1.data_1 IN (SELECT table_2.data_1 FROM table_2 WHERE table_1.data_1 = table_2.data_1);
I haven't tested this but think something like this would work:
UPDATE table_1 SET data_2 = NULL WHERE data_1 IN(SELECT table_1.data_1 FROM table_1, table_2 WHERE table_1.data_1 = table_2.data_1)
I love this resource for when I need different ideas on how to approach something: http://www.artfulsoftware.com/infotree/queries.php
UPDATE table_1 LEFT JOIN table_2 ON table_1.data_1 = table_2.data_1 AND table_1.data_1 IS NOT NULL SET table_1.data_2 = NULL;
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]