I have a scenario where i have to update 2 columns in a table form another table
I wrote the code as below
Set DWT00102_DERV_IMC_MISC.LAST_SPON_DT_KEY_NO =
FROM TABLE2 ),
(SELECT FIRST_SPONSOR_KEY_NO FROM TABLE2)
WHERE DWT00102_DERV_IMC_MISC.IMC_KEY_NO = TABLE2.IMC_KEY_NO
I am getting an error
"Table2.IMC_KEY_NO : invalid identifier"
I know why is the error coming but i am unable to find a solution for it.
Your main problem is, that the sub-selects will return more than one row (especially the first one) and that will give you an error ("single-row subquery returns more than one row")
You need to make sure that your subselect SELECT LAST_SPONSOR_KEY_NO FROM TABLE2 returns exactly one row. Either by using an aggregate function (min(), max(), ...) or by ensuring that you select from that table based on the primary key.
And you only need a single sub-select if the values for both columns are coming from the same row in TABLE2
So the statement might look like this
SET (last_spon_dt_key_no, first_spon_dt_key_no) =
WHERE imc_key_no = table2.imc_key_no);
This assumes that imc_key_no is the primary key in table2!