Скачать или смотреть How to Update SQL Column Based on Contains Search from a List of Values

Learn how to effectively update a SQL column by matching values from a predefined list. This guide will walk you through the process of updating and deleting records based on code matches in SQL.
---
This video is based on the question https://stackoverflow.com/q/63397445/ asked by the user 'user09' ( https://stackoverflow.com/u/5751045/ ) and on the answer https://stackoverflow.com/a/63397494/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Update SQL column based on contains search from list of values

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

Updating records in a database is a common task in SQL, but it can sometimes become challenging, especially when dealing with conditions like matching codes from a list. In this guide, we will address a specific problem: how to update a SQL column based on a contains search from a list of values and remove any records that do not match any values in that list.

The Problem

Imagine you have different varieties with unique codes, as shown below:

[[See Video to Reveal this Text or Code Snippet]]

You also have a table of existing data with varieties and codes, such as:

[[See Video to Reveal this Text or Code Snippet]]

The goal is to update the variety names in your table based on the matching codes. If a code does not belong to any of the varieties, that row should be deleted.

The Solution

To achieve this, we can use a combination of SQL UPDATE statements and DELETE statements. Here's a breakdown of the steps involved:

Step 1: Update the Variety Names

You will use a SQL UPDATE statement with a CASE expression to check against the codes for each variety:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query:

The CASE statement checks the code value and updates the variety column accordingly.

If a code matches a known variety, it assigns the appropriate name; if not, it sets the variety to NULL.

Step 2: Remove Unmatched Rows

After updating, your next step is to remove any rows that received a NULL value in the variety column:

[[See Video to Reveal this Text or Code Snippet]]

What This Does:

This deletes any rows where the variety column was not updated (i.e., codes that didn’t match any of the given varieties).

Conclusion

With these two simple SQL statements—an UPDATE followed by a DELETE—you can effectively manage your variety updates based on the list of codes. This method not only updates the existing records but also ensures that you remove any irrelevant entries from your table.

Implementing this solution will streamline your database management and ensure that your records are accurate. If you have any further questions, or if you'd like to share your experiences implementing this solution, feel free to leave a comment below!