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.
| Author |
Topic |
|
ts_abbott@hotmail.com
Starting Member
36 Posts |
 Posted - 2004-11-04 : 13:21:32
|
| Hi,I need to update one of my tables with info from another but I'm unsure how to go about it.These are the tables and sample data:CarStockId | MakeId | RangeId | ModelId | CarCode | Colour | Price1 | NULL | NULL | NULL | ABC12345 | basalt black | 659002 | NULL | NULL | NULL | ABV8uNHD | firedance orange | 14000CarLookupId | CarCode | MakeId | RangeId | ModelId1 | ABC12345 | 1 | 2 | 672 | ABV8uNHD | 5 | 6 | 39* this table always has all 5 fields completed.How would I go about writing an update statement to set the null values in CarStock to what they should be by joining CarStock.CarCode onto CarLookup.CarCode?Any help would be much appreciated. |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-11-04 : 13:23:14
|
| Why do you wish to store this info in 2 places?? |
 |
|
|
ts_abbott@hotmail.com
Starting Member
36 Posts |
Posted - 2004-11-04 : 13:38:58
|
quote:
Originally posted by ehorn
Why do you wish to store this info in 2 places??
for performance reasons mainly. i've got a query which matches user's alerts against carstock via carlookup but it's far to slow. i've now modified carstock to include all fields that i need to join user's alerts directly onto carstock but the xml feed i receive that updates carstock only contains a carcode which is why i need to run a query to fill in the missing fields.carlookup is essentially a table that contains every single car you can get down to the spec level (i.e. 1.8T gti 3dr). |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-11-04 : 14:04:26
|
something like this?update t1set MakeId = t2.MakeId, RangeId = t2.RangeId, ModelId = t2.ModelIdfrom CarStock t1 inner join CarLookup t2 on (t1.CarCode = t2.CarCode) Go with the flow & have fun! Else fight the flow  |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-11-04 : 14:11:48
|
With some filter criteria like this:where cs.makeid is null or cs.rangeid is null or cs.modelid is null |
|
|
|
ts_abbott@hotmail.com
Starting Member
36 Posts |
Posted - 2004-11-05 : 03:59:17
|
| thanks spirit1 & ehorn that's exactly the code I was looking for! i've just tested it and it runs perfectly! |
|
|
|
|
|
|
update a table with info from another