All Forums Database
nevermore 8 posts Joined 03/13
13 Sep 2013
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
 
 
 
 
 
 
 
 

M.Saeed Khurram 544 posts Joined 09/12
14 Sep 2013

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

Adharssh 36 posts Joined 08/13
14 Sep 2013

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.

Share the Knowledge. Feel the Happiness, When you share/Teach it.

nevermore 8 posts Joined 03/13
15 Sep 2013

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.
 
 

You must sign in to leave a comment.