0

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 DANA.

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

BagOfWords Table

USER_ID|DANA|LOVES|EAT
----------------------
   1  |  0  |  0  |  0

TableOfDocuments Table

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;
2
  • 2
    If you need to do such a thing, you need, in Oracle Dynamic SQL, and in mySQL Prepared Statements in SQL Scripts. Similar things for other databases. Standard SQL does not allow you to use the result of a SQL query in lieu of a column name. – joanolo Jan 15 '17 at 11:19
  • 2
    There is no easy way to decide dynamically which column name. Sounds like a poorly designed schema. – Rick James Jan 15 '17 at 16:00
1

You can not do that, you need dynamic SQL, and be careful with that. Below is a simple example (without any error handling, for educational purpose only).

declare
  l_col varchar2(128);
begin
  select word1 into l_col from TableOfDocuments WHERE ID=1;
  execute immediate 'UPDATE BagOfWords SET ' || l_col || ' = 1 WHERE USER_ID=1';
end;
/

Not the answer you're looking for? Browse other questions tagged or ask your own question.