I had a problem updating the subquery in which the subquery should return a value based on some key in the row of the updated cell. Usually he will work with the table as follows:
ββββββββββββββββββββββββββββββββ
β Key1 Key2 Value Other β
β βββββββββββββββββββββββββββββββ£
β Key11 Key21 Val1 Other1 β
β Key12 Key22 Val2 Other2 β
β Key13 Key23 Val3 Other3 β
β Key14 Key24 Val4 Other4 β
ββββββββββββββββββββββββββββββββ
And I would like to do something like:
UPDATE Table T1
SET T1.Value = (SELECT T2.Other
FROM Table T2
WHERE T2.Key2 IN ("SOME CONSTRAINT"))
WHERE T1.Key1 = T2.Key2
I know this will not work. The Outer where clause cannot see T2.Key2.
Otherwise, one table will be updated based on another table.
Tell me Table1
βββββββββββββββββββ
β Key Value β
β ββββββββββββββββββ£
β Key1 Val1 β
β Key2 Val2 β
β Key3 Val3 β
β Key4 Val4 β
βββββββββββββββββββ
AND Table2
ββββββββββββββββββββββββ
β OtherKey OtherValue β
β βββββββββββββββββββββββ£
β Key1 Val1 β
β Key2 Val2 β
β Key3 Val3 β
β Key4 Val4 β
ββββββββββββββββββββββββ
Again I want to do something like
UPDATE Table1 T1
SET T1.Value = (SELECT T2.Value
FROM Table2 T2
WHERE "SOME CONDITION")
WHERE T1.Key = T2.OtherKey
Again the external WHEREcannot see the key in the subquery. If I do this without external WHERE, in both cases I get an error that the subquery returns more than one value that is not allowed.
WHERE, INSERT UPDATE , UPDATE .
3- , , (Key, Value) , , , "" "" .