SQL Update 4 Lookups
Maybe this is a stupid beginner-question...
Someting I could do in Access (2, 97), but not in SQL Server 2000(example):
- In a table tblPCs I originally had a field with PCTypes (as text). I want
to replace it by a foreign key PCTypeID.
- I add the column PCTypeID to tblPCs, build a new table tblPCType with
- The problem is to insert the right PCTypeIDs into tblPCs. In access I
did it with this query, joining the two tables:
UPDATE DISTINCTROW tblPCs
LEFT JOIN tblPCTypes ON tblPCs.PCType = tblPCTypes.PCType
SET tblPCs.PCTypeID = tblPCTypes.PCTypeID
- In SQL Server this does not work (don't quite remember the errormessage
right now), he seems to like only "one record updates"!?
- What surprises me: when I did this once in Access and watched, what SQLServer
was doing (with a large table), it looked like 3 statements per row, not
like one SQL statement.
What is the right way to do it in SQL Server. If the answer is: do it on
a record by record base, I know how to do it (e.g. stored procedure with
cursor, walking through records or ADO recordset in VB-Program).
Anything better than that???
Thanks in advance.
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center