Update All SQL Null Values in Multiple Columns Using Column Level Where Clause

Update all SQL NULL values in multiple columns using Column level WHERE clause?

There isn't any convention to this -- if you want to only process records where respective columns are NULL, you need to use:

WHERE Answer_1 IS NULL 
   OR Answer_2 IS NULL 
   OR ...

But you could use this in the UPDATE statement:

UPDATE YOUR_TABLE
   SET col1 = COALESCE(col1, 99),
       col2 = COALESCE(col2, 99),
       col3 = ...

The logic is that the value will be updated to 99 only if the column value is NULL, because of how COALESCE works--returning the first non-NULL value (processing the list provided from left to right).

sql select update multiple column and check not null before update

The following query replaces the null values with table1 values and updates the rest from table2 values

UPDATE T1 SET T1.t1Name = IsNull(T2.t2Name,T1.t1Name),
              T1.t1Adress = IsNull(T2.t2Adress,T1.t1Adress),
              T1.t1Tel = IsNull(T2.t2Tel,T1.t1Tel)
        FROM table1 TI
                INNER JOIN table2 T2 ON T1.t1ID = T2.t2ID

Try the following select query to check your result

SELECT T1.t1Name,IsNull(T2.t2Name,T1.t1Name),T1.t1Adress,IsNull(T2.t2Adress,T1.t1Adress),T1.t1Tel,IsNull(T2.t2Tel,T1.t1Tel)
        FROM table1 TI
                INNER JOIN table2 T2 ON T1.t1ID = T2.t2ID   

Using IS NOT NULL for multiple columns

You can use

SELECT * FROM table1 
WHERE NOT (Column1 IS NULL OR 
Column2 IS NULL OR
 Column3 IS NULL OR
 Column4 IS NULL
    IS NOT NULL)

As per OP comment, Updating answer

Inserting Rows by Using INSERT and SELECT Subqueries

INSERT INTO Table_A
    SELECT column1, column2, column3,column4 
    FROM Table_B 
    WHERE NOT (Column1 IS NULL OR 
    Column2 IS NULL OR
    Column3 IS NULL OR
    Column4 IS NULL
    IS NOT NULL);

Your query

I am able to reduce 50 chars approx

SELECT * FROM AB_DS_TRANSACTIONS 
WHERE 
FK_VIOLATION IS NULL 
AND TRANSACTION_ID NOT 
IN(SELECT distinct TRANSACTION_ID FROM AB_TRANSACTIONS) 
AND 
NOT (
COUNTRY_ID IS NULL  
OR GEO_CUST_COUNTRY_ID IS NULL 
OR INVOICE_DATE IS NULL 
OR ABB_GLOBALID IS NULL 
OR SALES_ORG_ID IS NULL 
OR DIST_ID IS NULL 
OR CUSTOMER_ID IS NULL 
OR REPORT_UNIT_ID IS NULL 
OR CURR_INVOICE IS NULL 
OR DIVISION_CODE IS NULL
) 

Update multiple columns in MySQL but only when a value is not null

You can use if within your update clause:

update test_update set A=if(A is null, null, 'A2'), B=if(B is null, null, 'B2'), C=if(C is null, null, 'C2');

Example run:

MariaDB [test]> select * from test_update;
+------+------+------+
| A    | B    | C    |
+------+------+------+
| A1   | NULL | NULL |
| NULL | B1   | NULL |
| NULL | NULL | C1   |
| A1   | B1   | NULL |
| A1   | NULL | C1   |
| NULL | B1   | C1   |
| A1   | B1   | C1   |
+------+------+------+
7 rows in set (0.00 sec)

MariaDB [test]> update test_update set A=if(A is null, null, 'A2'), B=if(B is null, null, 'B2'), C=if(C is null, null, 'C2');
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

MariaDB [test]> select * from test_update;
+------+------+------+
| A    | B    | C    |
+------+------+------+
| A2   | NULL | NULL |
| NULL | B2   | NULL |
| NULL | NULL | C2   |
| A2   | B2   | NULL |
| A2   | NULL | C2   |
| NULL | B2   | C2   |
| A2   | B2   | C2   |
+------+------+------+
7 rows in set (0.00 sec)

SQl to Update columns when the fields are null

Use a multi-column update and regular expressions to parse the birthplace data on a comma.

update PERSONPROFILE pp
set (city, state, country) = (
  select nvl(REGEXP_SUBSTR (BIRTHPLACE, '[^,]+', 1, 1), 'UNKNOWN'), -- city
         nvl(REGEXP_SUBSTR (BIRTHPLACE, '[^,]+', 1, 2), 'UNKNOWN'), -- state
         nvl(REGEXP_SUBSTR (BIRTHPLACE, '[^,]+', 1, 3), 'UNKNOWN')  -- country
  from PERSON p
  where p.id = pp.id);

The regex matches 0 or more characters that do not match a comma, the first occurrence for city, 2nd occurrence for state, etc. Wrap it in NVL to set to "UNKNOWN" if the occurance is NULL.

UPDATE: I have since learned the regex expression '[^,]+' should be avoided as it fails when there are NULL elements in the list and you are selecting that element or one after it. Use this instead as it allows for NULLs:

update PERSONPROFILE pp
set (city, state, country) = (
  select nvl(REGEXP_SUBSTR (BIRTHPLACE, '([^,]*)(,|$)', 1, 1, NULL, 1)), 'UNKNOWN'), -- city
         nvl(REGEXP_SUBSTR (BIRTHPLACE, '([^,]*)(,|$)', 1, 2, NULL, 1)), 'UNKNOWN'), -- state
         nvl(REGEXP_SUBSTR (BIRTHPLACE, '([^,]*)(,|$)', 1, 3, NULL, 1)), 'UNKNOWN')  -- country
  from PERSON p
  where p.id = pp.id);

For more info see this post: Split comma seperated values to columns

---

Related Topics