Mysql update when is null

Here is an example:. In this case, the statement fails and rolls back. You cannot update a table and select directly from the same table in a subquery.

You can work around this by using a multi-table update in which one of the tables is derived from the table that you actually wish to update, and referring to the derived table using an alias.

Suppose you wish to update a table named items which is defined using the statement shown here:. As shown here, this statement does not work:. Instead, you can employ a multi-table update in which the subquery is moved into the list of tables to be updated, using an alias to reference it in the outermost WHERE clause, like this:. Because the optimizer tries by default to merge the derived table discounted into the outermost query block, this works only if you force materialization of the derived table.

In this case, the subquery is materialized by default rather than merged, so it is not necessary to disable merging of the derived table. Data Definition Statements. Atomic Data Definition Statement Support. LIKE Statement. Silent Column Specification Changes. Secondary Indexes and Generated Columns.

Data Manipulation Statements. Parenthesized Query Expressions. The Subquery as Scalar Operand. Comparisons Using Subqueries. Restrictions on Subqueries.

Transactional and Locking Statements. Statements That Cause an Implicit Commit. Restrictions on XA Transactions. It's really wierd — jim. Add a comment. Active Oldest Votes. Improve this answer. Daniel Vassallo Daniel Vassallo k 70 70 gold badges silver badges bronze badges. Thanks Daniel. I'm kind of convinced that this issue may lie in my application code — jim.

Gumbo Gumbo k gold badges silver badges bronze badges. Now, let me clarify. Through php I am using PDO and updating through a query. I've checked it twice. Let me look one more time. Yes, it is in fact null. I can see the NULL produced by my frnt end software in the data that should be null. General Failure 2, 3 3 gold badges 17 17 silver badges 45 45 bronze badges. You don't need the "where If a row already has null, and just use the first two lines of your show code, it will run just as fast, probably faster.

Rows that already have null, simply won't be written back to DB, because there is no change. Remember to look if your column can be null. Thiago Mata Thiago Mata 2, 30 30 silver badges 27 27 bronze badges. Phiter Daniel Dunn Daniel Dunn 77 8 8 bronze badges. After reading your answer, I ran the query and it executed as intended. Lost connection to MySQL server. Password Fails When Entered Interactively. Communication Errors and Aborted Connections. Can't initialize character set.

File Not Found and Similar Errors. Administration-Related Issues. Problems with File Permissions. How to Reset the Root Password. Case Sensitivity in String Searches. Problems with Column Aliases.