how to match a value of table01 with a column name in table02 and update that column value sql

Question

We have a table as

table01 (rollno, etcs100, etcs101, etcs102)
table02 (serialno, subjectcode)

I want to select a value from table02 (ie. subjectcode = "etcs100" ) and match this value from Column Name of table01 and update this column value by 1.

UPDATE TABLE01
SET (SELECT SUBJECTCODE 
     FROM table02 WHERE SERIALNUMBER = '1') 
         = (SELECT SUBJECTCODE FROM table02 
             WHERE SERIALNUMBER = '1') + 1
WHERE rollno = 1

We are looking for a query like this. we are using oracle database

possible duplicate of [Oracle - Update statement with inner join](http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join) — Mureinik, Jul 24 '15 at 06:46
@Mureinik - this question is *not* a duplicate of that question. The second table is a metadata table, which provides the column name to be used in the update. So, not a join. — APC, Jul 24 '15 at 07:18

score 0 · Accepted Answer · answered Jul 24 '15 at 06:47

You need to use dynamic SQL for this. The syntax varies according to database flavour. Here is the syntax for Oracle.

declare
    l_col_name varchar2(30);
begin
    SELECT SUBJECTCODE 
    into l_col_name
    FROM table02 
    WHERE SERIALNUMBER = '1';

    execute immediate
        'UPDATE TABLE01 SET '|| l_col_name || ' = '
              || l_col_name || ' + 1 WHERE rollno = 1'
    ;
end;

MySQL offers similar functionality with prepared statements. Find out more.

how to match a value of table01 with a column name in table02 and update that column value sql

1 Answers1