I'm trying to update values in table using column name from another table, why it is not working?
You can see that I have in table
TableOfDocuments, in column
WORD1, the name
Now I need to go to the other table where column name is
DANA and add 1
this is my code:
UPDATE BagOfWords SET (SELECT WORD1 FROM TableOfDocuments WHERE ID=1)=1 WHERE USER_ID=1
with this code I'm getting the column name in the other table (Dana):
SELECT WORD1 FROM TableOfDocuments WHERE ID=1
USER_ID|DANA|LOVES|EAT ---------------------- 1 | 0 | 0 | 0
ID|WORD1|WORD2|WORD3 -------------------- 1 | Dana| loves |pizza
So, after getting help is it possible to do something like this in order not to type WORD1---WORD5 separately? Obviously I tried but this doesn't work, what do I need to add or change in order to make this work?
declare l_col varchar2(128); begin select word1,word2,word3,word4,word5 into l_col from TableOfDocuments WHERE ID=1; execute immediate 'UPDATE BagOfWords SET ' || l_col || ' = 1 WHERE USER_ID=1'; end;