0

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

Belliez
  • 5,356
  • 12
  • 54
  • 62
  • sorry to digress a bit, but this looks like an EAV pattern. Shouldn't the table be like this ID Color Speed age ? You might run into serious performance issues later on. – no_one Jan 07 '10 at 11:06
  • its based on the answer from a previous question of mine and I want to update all measurements from the measurement table PropertyType.Name to a single value: http://stackoverflow.com/questions/1762305/sql-server-one-table-with-400-columns-or-40-tables-with-10-columns – Belliez Jan 07 '10 at 11:11

2 Answers2

2
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
  • 28,925
  • 4
  • 72
  • 77
2

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
  • 9,567
  • 9
  • 35
  • 49
  • 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 Jan 07 '10 at 12:30
  • ok, adding "FROM table2" before the JOIN makes this work. Thank you for your help. – Belliez Jan 07 '10 at 12:44