Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

update sql

Posted on 2008-11-01
10
Medium Priority
?
399 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 46

Accepted Solution

by:
Kent Olsen earned 2000 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 46

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 46

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 46

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 46

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

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Loops Section Overview
Suggested Courses

971 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