Are you working with an SQL database where you need to copy data from one table to another? Suppose you have a main table that describes movies. You have another table that has rating data for some of the movies. The main movie table has a primary key that uniquely identifies each movie. That’s good. The other table has a column that contains data that uniquely defines each movie. Instead of having these two tables you’d like to create a column in the main table to store the ratings that you copied over from that rating table. You know that not all movies have ratings, but you know that you can build a query that copies the data over for the movies that you do have.

Your tables will be different than this example, but once you understand how this script and these queries work, and you’ve tested these, you’ll be able to write your own queries.

Let’s do this by example, in a non-production server.

CREATE TABLE [dbo].[MovieRatings](
	[MovieId] [int] NOT NULL,
	[Rating] [float] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Movies]    Script Date: 2021-11-23 7:56:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Movies](
	[MovieId] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](50) NULL,
	[Rating] [float] NULL,
 CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED 
(
	[MovieId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[MovieRatings] ([MovieId], [Rating]) VALUES (1, 8.1)
GO
INSERT [dbo].[MovieRatings] ([MovieId], [Rating]) VALUES (7, 7.7)
GO
SET IDENTITY_INSERT [dbo].[Movies] ON 
GO
INSERT [dbo].[Movies] ([MovieId], [Title], [Rating]) VALUES (1, N'Gone With the Breeze', NULL)
GO
INSERT [dbo].[Movies] ([MovieId], [Title], [Rating]) VALUES (2, N'Star Battles', 8.4)
GO
INSERT [dbo].[Movies] ([MovieId], [Title], [Rating]) VALUES (3, N'Bridge on the River Kiwi', NULL)
GO
SET IDENTITY_INSERT [dbo].[Movies] OFF
GO

SELECT * FROM Movies
SELECT * FROM MovieRatings

SELECT Movies.MovieId, Title, Movies.Rating
FROM Movies
INNER JOIN MovieRatings
ON Movies.MovieId = MovieRatings.MovieId;

Below is a screenshot in SSMS.

Let’s copy the data over with the following script. Before writing this query we need to know the names of the two tables, the name of the column we need to update (copy to), the name of the column we are copying from and finally we need to know the primary and foreign keys so that we can make the join (link).

UPDATE Movies
SET Movies.Rating = MovieRatings.Rating
FROM Movies 
INNER JOIN
MovieRatings ON Movies.MovieId = MovieRatings.MovieId;

SELECT MovieId, Title, Rating
FROM Movies

Below is the screen shot in SSMS.

Left and Right Joins

In the above example, I think of putting the two table together left and right and matching up the MovieId. Let’s do that in a couple of queries.

SELECT Movies.MovieId, Movies.Title, Movies.Rating, MovieRatings.MovieId, MovieRatings.Rating
FROM Movies
LEFT JOIN MovieRatings
ON Movies.MovieId = MovieRatings.MovieId;

SELECT MovieRatings.MovieId, MovieRatings.Rating, Movies.MovieId, Movies.Title, Movies.Rating
FROM Movies
RIGHT JOIN MovieRatings
ON Movies.MovieId = MovieRatings.MovieId;

Here is the screenshot.