help in update select statement.

[bcdixit]

I am running this query and basically I want to insert the row count values from diff_x and diff_y into tl_compare table. this query bombs on me.

UPDATE bi_stg_1.TL_COMPARE
from diff_x x, diff_y y
SET
count1 = ( select count(*) from x),
count2 = ( select count(*) from y)
WHERE DATABASE_NAME='stg' AND TABLE_NAME='delta' AND RUN_DATE=date AND INSTANCE1=8 AND INSTANCE2=7;

i get a Failure 3706 Syntax error: expected something between '(' and the 'select' keyword.

any alternatives or pointers to a solution?

 

[al1024]

Try this :

UPDATE  bi_stg_1.tl_compare
FROM    (   SELECT  COUNT(*) AS cnt 
            FROM    diff_x
        )   AS x
    ,   (   SELECT  COUNT(*) AS cnt 
            FROM    diff_y
        )   AS  y
SET     count1 = x.cnt
    ,   count2 = y.cnt
WHERE   database_name = 'stg' 
    AND table_name  = 'delta' 
    AND run_date    = CURRENT_DATE 
    AND instance1   = 8 
    AND instance2   = 7
;

 

[bcdixit]

yes, this worked for me.
thanks