How to Update Table Records Based on Matching IDs in SQL?

Update SQL table records based on ID matching

Introduction

In database management, sometimes it is necessary to update data in one table based on matching IDs in another table. This is particularly useful when migrating data or synchronizing information between tables.

Problem Description

Suppose we have a database with two tables, Sales_Import and RetrieveAccountNumber, each table contains a column named LeadID. The Sales_Import table has a AccountNumber column that needs to be updated with the matching LeadID value from the RetrieveAccountNumber table based on the matching AccountNumber value.

Failed attempt

The initial attempt to update a record using the following query failed:

<code class="language-sql">UPDATE [Sales_Lead].[dbo].[Sales_Import]
SET    [AccountNumber] = (SELECT RetrieveAccountNumber.AccountNumber
                          FROM   RetrieveAccountNumber
                          WHERE  [Sales_Lead].[dbo].[Sales_Import]. LeadID =
                                                RetrieveAccountNumber.LeadID)</code>

This query replaces the account number with a NULL value.

Solution

The solution to this problem lies in using the UPDATE FROM syntax combined with join operations. This approach allows us to update records in one table based on matching values ​​in another table. The following are SQL queries for different database systems:

MySQL and MariaDB

<code class="language-sql">UPDATE
    Sales_Import SI,
    RetrieveAccountNumber RAN
SET
    SI.AccountNumber = RAN.AccountNumber
WHERE
    SI.LeadID = RAN.LeadID;</code>

MS SQL

<code class="language-sql">UPDATE
    Sales_Import
SET
    Sales_Import.AccountNumber = RAN.AccountNumber
FROM
    Sales_Import SI
INNER JOIN
    RetrieveAccountNumber RAN
ON
    SI.LeadID = RAN.LeadID;</code>

The above is the detailed content of How to Update Table Records Based on Matching IDs in SQL?. For more information, please follow other related articles on the PHP Chinese website!