Update for Derive Tables

Database
Enthusiast

Update for Derive Tables

Hi,

I've 2 derived tables and want to update one column. And I couldnt find a way to mak this ok.

Can you help me pls. thanks in advance.

UPDATE TGT

FROM 

SELECT

COL1, COL2, COL3, COL4 

FROM TABLE_1 T1

INNER JOIN TABLE_2 T2 ON T1.ID = T2.ID

INNER JOIN TABLE_3 T3 ON T2.ID = T3.ID) TGT ,

(

SELECT

COL1, COL2, COL3, COL4 

FROM TABLE_4 T4

INNER JOIN TABLE_5 T5 ON T4.ID = T5.ID

INNER JOIN TABLE_6 T6 ON T5.ID = T6.ID

) LRY

SET  T1.COL55 = 33

WHERE  LRY.COL1 = TGT.COL1

3 REPLIES
Enthusiast

Re: Update for Derive Tables

Hi,

I have checked you statement with a sample scenario and it is working fine. The following query should work for you.

I have made just a little change, you were refering the COL55 by qualifying with T1, which I think is incorrect. because T1 is the alias of inner table of the derived TGT. Everything else looks fine. 

UPDATE TGT
FROM
(
SELECT
COL1, COL2, COL3, COL4
FROM TABLE_1 T1
INNER JOIN TABLE_2 T2 ON T1.ID = T2.ID
INNER JOIN TABLE_3 T3 ON T2.ID = T3.ID
) TGT ,
(
SELECT
COL1, COL2, COL3, COL4
FROM TABLE_4 T4
INNER JOIN TABLE_5 T5 ON T4.ID = T5.ID
INNER JOIN TABLE_6 T6 ON T5.ID = T6.ID
) LRY
SET COL55 = 33
WHERE LRY.COL1 = TGT.COL1

Further can you please tell details of the actual error you are facing?

Khurram
Enthusiast

Re: Update for Derive Tables

Hi,

Can You please let us know,  What error did you faced? The Update Query is trying to Update the COLUMN 'COL55' but the SubQuery doesn't select the COL55.

Thanks & Regards,

Adharssh.

Enthusiast

Re: Update for Derive Tables

Hi,

When I used the upper query for update, I get the error "Derived table not allowed to update".

So I stop using derived table and convert my query as shown below :

UPDATE T1
FROM
TABLE_1 T1 ,
TABLE_2 T2 ,
TABLE_3 T3 ,
(
SELECT
COL1, COL2, COL3, COL4
FROM TABLE_4 T4
INNER JOIN TABLE_5 T5 ON T4.ID = T5.ID
INNER JOIN TABLE_6 T6 ON T5.ID = T6.ID
) LRY
SET COL55 = 33 -- T1 table's column
WHERE LRY.COL1 = TGT.COL1 AND
T1.ID = T2.ID AND
T2.ID = T3.ID

Thank you all.