How to handle NULLs when updating one table from another using MySQL


Problem :

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);


Solution :

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:

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;

Mysql Tutorials

Mysql Howto..

How to replace part of my text field in MySQL that has variable number appended

How can I see the auto-generated SQL query for DB schema update with Doctrine2 and Symfony2?

How to filter (or replace) unicode characters that would take more than 3 bytes in UTF-8?

How to save user input linebreak to database. (jQuery/php/mysql)

How to unset BLOB with php/mysql?

JDBC and MySQL, how to persist java.util.Date to a DATETIME column?

How to select same value fields in mysql? [closed]

how to sum all values in mysql using php

Silex and Doctrine mysql: how to return results from select all

How do I add data from a form with a variable number of rows into a MySQL table?

how to get the tag list in wordpress mysql

How to keep mysql connection open for multiple get requests in PHP?

How do I decrypt and encrypt Joomla user password for Java program?

How to order data by custom algorithm in MySQL?

How to 'undo' this small function in PHP?

SQL: how to get sum() of counted values [duplicate]

How to get next alpha-numeric ID based on existing value from MySQL

How to optimize foreach loop in PHP

How to group data in MySQL for every 3 hours from today?

How to handle NULL values in mysql/php?

MySQL query help: how to pivot this table?

Ranking in MySQL, how do I get the best performance with frequent updates and a large data set?

How to convert Blob data in MYSQL to Android ImageView

How Can i Connect C#.net desktop application on My Computer to the MYSQL server of another computer

MySql datetime field in HTML/php table is only showing date when using form

how to enforce a constraint in mysql?

How to prevent mysql sleep injections? [closed]

How to use MySQL like operator in JDBC?

How to get the count of records with the highest date as a given date in mysql query

How to optimize this MySQL table?