dark

Update Data in SQL from One Table to Another Based on ID Match

Update Data in SQL from One Table to Another Based on ID Match

Updating data in SQL is a common task in database management. Sometimes, we need to update data in one table based on the values of another table. In this tutorial, you will learn how to update data in SQL from one table to another based on an ID match.

Step 1: Understand the Problem

Before you start writing SQL code, you need to understand the problem you are trying to solve. In this case, you want to update data in one table based on the values of another table. Specifically, you want to update the values in Table A based on the values in Table B where the IDs match.

Step 2: Prepare the Tables

To demonstrate how to update data in SQL from one table to another based on an ID match, we will create two tables: Table A and Table B. Table A will contain the data we want to update, and Table B will contain the data we want to use to update Table A.

Here is the SQL code to create Table A and Table B:

CREATE TABLE TableA (
ID INT PRIMARY KEY,
Name VARCHAR(255),
Age INT
);

CREATE TABLE TableB (
ID INT PRIMARY KEY,
Name VARCHAR(255),
Age INT
);

Step 3: Insert Data into the Tables

Now that we have created the tables, we need to insert some data into them. Here is the SQL code to insert data into Table A and Table B:

INSERT INTO TableA (ID, Name, Age)
VALUES (1, 'John', 25),
(2, 'Jane', 30),
(3, 'Bob', 35);

INSERT INTO TableB (ID, Name, Age)
VALUES (1, 'John', 26),
(2, 'Jane', 29),
(3, 'Bob', 36);

Step 4: Update Data in Table A from Table B

Now that we have created and populated the tables, we can update data in Table A from Table B based on an ID match. Here is the SQL code to update data in Table A from Table B:

 UPDATE TableA
 SET Age = TableB.Age
 FROM TableA
 INNER JOIN TableB ON TableA.ID = TableB.ID;

Explanation of the SQL Code:

  • The UPDATE statement is used to update data in Table A.
  • The SET clause is used to specify the column we want to update in Table A.
  • The FROM clause is used to specify the table we want to join with Table A.
  • The INNER JOIN clause is used to join Table A with Table B based on the ID column.
  • The ON clause is used to specify the condition for the join.
  • The TableB.Age column is used to update the Age column in Table A.

Step 5: Verify the Results

To verify that the data has been updated in Table A, we can run a SELECT statement on Table A. Here is the SQL code to select all data from Table A:

SELECT * FROM TableA;

The output should look like this:

ID | Name | Age
 ---|------|----
 1  | John | 26
 2  | Jane | 29
 3  | Bob  | 36

Updating data in SQL from one table to another based on an ID match is a common task in database management. In this tutorial, you learned how to update data in SQL from one table to another based on an ID match using the UPDATE statement with the INNER JOIN clause. Remember to always understand the problem you are trying to solve before writing SQL code.

Related tutorials