views:

97

answers:

2

Hi,

I have three tables and I want to update all values for a particular type to the same value:

table1: id, ValueType

table2: id, Value

table3: id, fkValueTypeId, fkValueId

fkValueType references ID in table1. fkValue references ID in Table2

I am trying to set all Speed values to the same value:

i.e.

Table1:
0, speed
1, age
2, colour

Table2:
0, 10
1, 20
2, 30
3, 40
4, 18
5, 18
6, blue
7, black
8, orange
9, 33
10, 34
11, 35

Table3:
0, 0, 0      --Speed = 10
1, 0, 0      --Speed = 20
2, 0, 0      --Speed = 30
3, 0, 0      --Speed = 40
4, 1, 1      --Age = 18
5, 1, 1      --Age = 18
6, 2, 2      --Colour = Blue
7, 2, 2      --Colour = Black
8, 2, 2      --Colour = Orange
9, 0, 9      --Speed = 33
10, 0, 10    --Speed = 34
11, 0, 11    --Speed = 35

What I want to do is Update Speed to '55' for all Speed entries in the tables so that table2 looks like this:

Table2:
0, 55
1, 55
2, 55
3, 55
4, 18
5, 18
6, blue
7, black
8, orange
9, 55
10, 55
11, 55

Hope this makes sense. I am not sure on the syntax and can do it using a loop but wondered if there is a better way (which I am sure there is!).

Thank you

+2  A: 
UPDATE table2
SET table2.Value = 55
FROM table2
JOIN table3 ON table3.fkValueId = table2.id
WHERE table3.fkValueTypeId = 0

Edit: wasn't aware of SQL server's syntax warts :)

hobodave
Thats similar to what I was trying and I get the following error:Incorrect syntax near the keyword 'JOIN'.
Belliez
adding "FROM table2" before the JOIN makes this work. Thank you for your help.
Belliez
I was winging it using standard SQL, wasn't aware of sql server's warts. :)
hobodave
+2  A: 

A rewrite of @hobodave's answer:

UPDATE table2
SET Value = 55
FROM table2
JOIN table3 ON table3.fkValueId = table2.id
WHERE table3.fkValueTypeId = 0
Jonas Lincoln
I changed my syntax to match this and still have "Incorrect syntax near the keyword 'JOIN'". Adding FROM Table2 just before the Join gets rid of the error but I am still trying to get this working.
Belliez
ok, adding "FROM table2" before the JOIN makes this work. Thank you for your help.
Belliez
Yes, of course, I'll update my answer.
Jonas Lincoln