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
    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
0

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;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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