# Average from one table to another

• ### Question

• Hi,

I'm new to SQL and trying to understand the logic of working with more than one table.

I wish to take the average value of one table's column and UPDATE it as the value for a whole column on another table.

For example:

Table Band: Id, name, Height

Table Whatever: Id, BandAvg

I wish to set the average height from table Band to column 'BandAvg'.

What shall be the best way? is there a way to set a variable that both tables can access or is there any better way to do so?

Tuesday, November 12, 2019 6:52 AM

• And, the below is the another possible way to achieve the same.

```WITH CTE AS
(SELECT
Id
,AVG(Height) BandAvg
FROM Band
GROUP BY Id)

UPDATE tbla
SET tbla.BandAvg = tblb.BandAvg
FROM Whatever tbla
INNER JOIN CTE tblb
ON tbla.Id = tblb.Id```

Thank you

Tuesday, November 12, 2019 7:23 AM

### All replies

• Hi

```UPDATE tbla
SET tbla.BandAvg = tblb.BandAvg
FROM Whatever tbla
INNER JOIN
(SELECT
Id
,AVG(Height) BandAvg
FROM Band
GROUP BY Id)tblb
ON tbla.Id = tblb.Id```

Thank you

Tuesday, November 12, 2019 7:22 AM
• And, the below is the another possible way to achieve the same.

```WITH CTE AS
(SELECT
Id
,AVG(Height) BandAvg
FROM Band
GROUP BY Id)

UPDATE tbla
SET tbla.BandAvg = tblb.BandAvg
FROM Whatever tbla
INNER JOIN CTE tblb
ON tbla.Id = tblb.Id```

Thank you

Tuesday, November 12, 2019 7:23 AM
• Thanks! worked out for me :)
Tuesday, November 12, 2019 8:27 AM
• Hi

Do click on "Mark as Answer" and Upvote on the posts that helps you , this can be beneficial to other community members.

Thank you

Tuesday, November 12, 2019 9:06 AM
• Hi GBTiger,