update a field based off of another table

I have 1 table with a list of car makes and an ID.
then I have a list of models with a column for carid that matches the first table.

I was wondering  how I can update the model table and replace all the ids with the name from the make table where the makeid matches the other table.

Would something like this work?
update carmodels set carmodels.makeid = carmakes.carname where carmodels.makeid = carmakes.carid

Open in new window

rivkamakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
The T-SQL syntax is

UPDATE c
SET makeID = O.carName
FROM carmodels C
  INNER JOI carmakes M ON C.makeID = M.makeID;

Open in new window


But why want you do this? You can use a JOIN anytime you need this information. Also it is semantically wrong, cause an ID is not a name. Thus this kind of update is not a good idea. It introduces also redundancy, which is a unwanted situation in the relational model, because it opens the model to get inconsistent.
Scott PletcherSenior DBACommented:
1) You would not want to do that.  If you do want to put the name in the other table, you should create a corresponding column for it, not put a name into an "id" column.
2) I doubt you could put the full name in an id column anywhere.  Presumably the id column is either numeric or much shorter than the name value anyway.
rivkamakAuthor Commented:
Right now I am using 2 sql statmenets and I am trying to figure out how to combine it into 1.
select * from CarMakes where name= ' getname' and type='automobile'
then
select * from CarModels where makeid = '"  & (makeList.Fields.Item("carid").Value)  & "' order by model

can i do this with 1 join?
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you want only show the car make then isn't an update but a select with a join on both tables:
SELECT  carmakes.carname,  carmodels.modelname 
FROM carmakes
INNER JOIN carmodels ON carmodels.carid = carmakes.carid

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.