Avatar of rivkamak
rivkamak
Flag for United States of America asked on

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

Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
ste5an

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 Pletcher

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.
rivkamak

ASKER
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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.