Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

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
0
nav29
Asked:
nav29
  • 5
  • 4
1 Solution
 
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now