Solved

update sql

Posted on 2008-11-01
10
392 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:nav29
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 22859857
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22859863
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
 

Author Comment

by:nav29
ID: 22861105
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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22861181
Actually, that looks pretty close.  :)


Kent
0
 

Author Comment

by:nav29
ID: 22861416
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22861504
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
 

Author Comment

by:nav29
ID: 22861726
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22861896
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
 

Author Comment

by:nav29
ID: 22862038
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22862335
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help on Db2 Stored procedures 6 144
DB2 join tables across schemas 5 645
Dynamic Query Need 9 96
CATALOG A DB2 TCPIP NODE with AN ALIAS 2 151
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question