Solved

update sql

Posted on 2008-11-01
10
388 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
  • 5
  • 4
10 Comments
 
LVL 45

Accepted Solution

by:
Kdo 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
 
LVL 45

Expert Comment

by:Kdo
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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:Kdo
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

914 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now