Sql语法:根据不同表中的值更新表中的所有值

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

我有三个表，我想把一个特定类型的所有值更新为相同的值:

table1: id, ValueType

表1:id,ValueType

table2: id, Value

表二:id值

table3: id, fkValueTypeId, fkValueId

table3:id、fkValueTypeId fkValueId

fkValueType references ID in table1. fkValue references ID in Table2

表1中的fkValueType引用ID。表2中的fkValue引用ID

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:

我要做的是将表中所有速度项的速度更新为'55'因此表2看起来是这样的:

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 个解决方案

#1

A rewrite of @hobodave's answer:

重写@hobodave的答案:

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

#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 :)

编辑:不知道SQL server的语法错误:)

#1