update sql

hi there
any idea whats wrong with thos sql

UPDATE DMART_MKT2.DMT_TACTICS_FT_1 A SET DMT_OPPRTNTY_ID= SELECT    
   T10.DMT_OPPRTNTY_ID
    FROM
(dmart_mkt1.STG_TACTICS T1 LEFT OUTER JOIN dmart_mkt2.DMT_OPPORTUNITY_DM T10
       ON  
      T10."OPPRTNTY_NUM" = T1."OPP_NO" )
WHERE A.DMT_TACTIC_ID= T1.TACTIC_ID;

UPDATE DMART_MKT2.DMT_TACTICS_FT_1 A SET DMT_OPPRTNTY_ID= SELECT T10.DMT_OPPRTNTY_ID FROM (dmart_mkt1.STG_TACTICS T1 LEFT OUTER JOIN dmart_mkt2.DMT_OPPORTUNITY_DM T10 ON T10."OPPRTNTY_NUM" = T1."OPP_NO" ) WHERE A.DMT_TACTIC_ID= T1.TACTIC_ID
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "T10" was found following "_OPPRTNTY_ID=
SELECT".  Expected tokens may include:  "<space>".  SQLSTATE=42601

SQL0104N  An unexpected token "T10" was found following "_OPPRTNTY_ID= SELECT".  Expected tokens may include:  "<space>                                      ".

Explanation:

A syntax error in the SQL statement was detected at the specified
token following the text "<text>".  The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.  

 As an aid to the programmer, a partial list of valid tokens is
provided in the SQLERRM field of the SQLCA as "<token-list>".  
This list assumes the statement is correct to that point.  

 The statement cannot be processed.  

User Response:

Examine and correct the statement in the area of the specified
token.  

 sqlcode :  -104

 sqlstate :  42601
nav29Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi nav,

Encapsulate the subquery in parenthesis.


Kent

UPDATE DMART_MKT2.DMT_TACTICS_FT_1 A SET DMT_OPPRTNTY_ID = 
  (SELECT T10.DMT_OPPRTNTY_ID
   FROM dmart_mkt1.STG_TACTICS T1 
   LEFT OUTER JOIN dmart_mkt2.DMT_OPPORTUNITY_DM T10 
     ON T10."OPPRTNTY_NUM" = T1."OPP_NO" 
   WHERE A.DMT_TACTIC_ID= T1.TACTIC_ID);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HoggZillaCommented:
Move your open parenthesis before the SELECT instead of before the dmart_mktl.....
UPDATE DMART_MKT2.DMT_TACTICS_FT_1 A SET DMT_OPPRTNTY_ID = 
(SELECT    
   T10.DMT_OPPRTNTY_ID
    FROM 
dmart_mkt1.STG_TACTICS T1 LEFT OUTER JOIN dmart_mkt2.DMT_OPPORTUNITY_DM T10 
       ON  
      T10."OPPRTNTY_NUM" = T1."OPP_NO" )
WHERE A.DMT_TACTIC_ID= T1.TACTIC_ID;

Open in new window

0
nav29Author Commented:
Hi there,
one thing more i just want to know how much rows will be updated when i run this update before running this update, Can this update be modified sumthing like this so that i can count before update

Thanks

SELECT    
   count(*)
    FROM
dmart_mkt1.STG_TACTICS T1 LEFT OUTER JOIN dmart_mkt2.DMT_OPPORTUNITY_DM T10 ,DMART_MKT2.DMT_TACTICS_FT_1 A
       ON  
      T10."OPPRTNTY_NUM" = T1."OPP_NO" )
WHERE A.DMT_TACTIC_ID= T1.TACTIC_ID;
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Kent OlsenData Warehouse Architect / DBACommented:
Actually, that looks pretty close.  :)


Kent
0
nav29Author Commented:
Hi Kdo,
getting error below

SELECT    
   count(*)
    FROM
dmart_mkt1.STG_TACTICS T1 LEFT OUTER JOIN dmart_mkt2.DMT_OPPORTUNITY_DM T10 ,DMART_MKT2.DMT_TACTICS_FT_1 A
       ON  
      T10."OPPRTNTY_NUM" = T1."OPP_NO" )
WHERE A.DMT_TACTIC_ID= T1.TACTIC_ID;
------------------------------------------------------------------------------
SELECT count(*) FROM dmart_mkt1.STG_TACTICS T1 LEFT OUTER JOIN dmart_mkt2.DMT_OPPORTUNITY_DM T10 ,DMART_MKT2.DMT_TACTICS_FT_1 A ON T10."OPPRTNTY_NUM" = T1."OPP_NO" ) WHERE A.DMT_TACTIC_ID= T1.TACTIC_ID
SQL0104N  An unexpected token "," was found following "T_OPPORTUNITY_DM T10".  
Expected tokens may include:  "JOIN".  SQLSTATE=42601

SQL0104N  An unexpected token "," was found following "T_OPPORTUNITY_DM T10".  Expected tokens may include:  "JOIN                                           ".
Thanks
0
Kent OlsenData Warehouse Architect / DBACommented:
Ok,

Try this one.
SELECT    
   count(*)
FROM dmart_mkt1.STG_TACTICS T1 
LEFT OUTER JOIN dmart_mkt2.DMT_OPPORTUNITY_DM T10 
   ON T10."OPPRTNTY_NUM" = T1."OPP_NO"
INNER JOIN DMART_MKT2.DMT_TACTICS_FT_1 A
   ON A.DMT_TACTIC_ID= T1.TACTIC_ID;

Open in new window

0
nav29Author Commented:
Thanks,
1) it returns 3000 rows does this mean 3000 should be updated ? currently stg_tactics has 3000 rows and dmt_tactics_ft_1  also has 3000 rows,

i think it should return 0 right?

SELECT    
   count(*)
FROM dmart_mkt1.STG_TACTICS T1
LEFT OUTER JOIN dmart_mkt2.DMT_OPPORTUNITY_DM T10
   ON T10."OPPRTNTY_NUM" = T1."OPP_NO"
INNER JOIN DMART_MKT2.DMT_TACTICS_FT_1 A
   ON A.DMT_TACTIC_ID= T1.TACTIC_ID;
0
Kent OlsenData Warehouse Architect / DBACommented:
You can see which rows by selecting the primary keys instead of the count.  Or, of course, the entire row.


Kent

SELECT t1.{key identifier}
FROM dmart_mkt1.STG_TACTICS T1 
LEFT OUTER JOIN dmart_mkt2.DMT_OPPORTUNITY_DM T10 
   ON T10."OPPRTNTY_NUM" = T1."OPP_NO"
INNER JOIN DMART_MKT2.DMT_TACTICS_FT_1 A
   ON A.DMT_TACTIC_ID= T1.TACTIC_ID;

Open in new window

0
nav29Author Commented:
hi Kent,
key for update id tactic_id from stg_tactics varchar 56  and key from dmt_tacticts_ft_1 is dmt_tactic_id
varchar 256 ,
and i geting same tactic_id and dmt_tactic_id when i run query above

i mean tactic_id    dmt_tactic_id
              256              256
               456             456
and so on all same 3000 rows

its still giving me count 3000for query above.

is it  varchar length issue ?
0
Kent OlsenData Warehouse Architect / DBACommented:
That suggests that there is a one-for-one match between the keys in the two tables.

The query starts with all of the rows in dmart_mkt1.STG_TACTICS and outer joins dmart_mkt2.DMT_OPPORTUNITY_DM to it.  If STG_TACTICS started with 3,000 rows the result of the join will be at least 3000 rows.  Where the keys don't match you'll still get a row in the result set, and if a key is matched multiple times you'll get multiple rows.  The last thing that the query does is inner join DMART_MKT2.DMT_TACTICS_FT_1 to the results of the first join.  If the first table (STD_TACTICS) and the last table (DMT_TACTICS_FT_1) have the same keys, the result will be a table will all of the keys from the first table.

You might want to filter the results a bit.


Kent
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.