Update table by grabbing data from other table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update table by grabbing data from other table

Author

Topic

theKid27
Starting Member

21 Posts

Posted - 2013-03-20 : 02:21:13

Hi Experts/Members,

I have a table that I would like to update I'll call it Table A and Table B.

Table A

CarType Color
------------------
Car 1 NULL
Car 2 NULL
Car 3 NULL

Table B

CarType Color
------------------
Car 1 Red
Car 2 Blue
Car 3 Green

I would like to write an update statement to update in Table A, which will then become like the below.

CarType Color
------------------
Car 1 Red
Car 2 Blue
Car 3 Green

I have tried a very manual way which i find it very hard to do it as i will have to do write it for each color.. my statement is as below:

UPDATE Table A
SET Table A.[Color]='Red'
Where Table A.[CarType] In (SELECT [CarType] FROM Table B)

Hope that you all can advice.. Appreciate for the help

Thanks

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-20 : 03:16:54

try this

update tablea a set a.color=b.color
inner join tableb b
on a.cartype=b.cartype

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-20 : 03:20:29

[code]
CREATE Table A (CarType VARCHAR(10), Color varchar(10))
INSERT INTO A
SELECT 'Car 1', NULL UNION ALL
SELECT 'Car 2', NULL UNION ALL
SELECT 'Car 3', NULL

CREATE Table B (CarType VARCHAR(10), Color varchar(10))
INSERT INTO B
SELECT 'Car 1', 'Red' union all
SELECT 'Car 2', 'Blue' union all
SELECT 'Car 3', 'Green'

--Try this
Update A
SET a.Color = b.Color
FROM A
JOIN B ON a.CarType = b.CarType

SELECT * FROM A[/code]

--
Chandu

theKid27
Starting Member

21 Posts

Posted - 2013-03-20 : 05:28:16

Thanks all.. it works

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-20 : 05:40:06