Question 239606 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Query to Set/Match the Null Values for a Column Based on If a Sub Field is Included/Contained in a Main Fields Identifier

I have something like the table below that is incredibly large.

Where the "Category" column is Null, I need to return the Category based on the rows where category is NOT null. The logic should be that IF the Category is NULL and the Sub_Identifier is CONTAINED in the main identifier string, THEN that matching Category should be set for that line item in the row.

Chart Image from Excel

Based on the example chart, I’m expecting something like this to return.

Excel Chart

The part where I am stuck is related to returning/setting the matches in the Category column for the Sub_Identifiers.

Tags:

2

Answers


  1. 0 
    select a.Sub_identifier, a.Main_Identifier, coalesce(a.Category, b.category) as Category
from YourTable a
inner join YourTable b
on a.Sub_identifier = b.Sub_identifier
and b.category is not null
    Login or Signup to reply.
  2. 0

    A cleaner and easier to understand approach to solve this –

    UPDATE YourTable AS t1
SET Category = t2.Category
FROM YourTable AS t2
WHERE t1.Category IS NULL
  AND t2.Category IS NOT NULL
  AND POSITION(t2.Sub_Identifier IN t1.Main_Identifier) > 0;

    Firstly, we update the "Category" column in "YourTable" table (aliased as t1) and then; using a self-join with another instance of the "YourTable" table (aliased as t2), we’re checking for rows where the "Category" in the target row (t1) is null and the "Category" in the source row (t2) is not null. We’re also using the POSITION function to check if the "Sub_Identifier" in the source row (t2) is contained within the "Main_Identifier" of the target row (t1). If the conditions are met, we’re updating the "Category" in the target row with the "Category" from the source row.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search