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

x
?
Solved

Help with query

Posted on 2005-04-05
13
Medium Priority
?
1,247 Views
Last Modified: 2012-05-05
Basically I need to make this MS SQL Server query work with DB2:

UPDATE tbl1 SET fld1 = 'value'
WHERE fld2 IN
      (
            SELECT TOP 100 fld2
            FROM tbl1
            WHERE fld3 = ''
      )

I have no DB2 experience so I though this would work:

UPDATE tbl1 SET fld1 = 'value'
WHERE fld2 IN
      (
            SELECT fld2
            FROM tbl1
            WHERE fld3 = ''
            FETCH FIRST 100 ROWS ONLY
      )

However it doesn't. I get "SQL0199: Keyword FETCH not expected. Valid tokens: ) UNION. Cause . . . . . : The keyword FETCH was not expected here. A syntax error was detected at keyword FETCH...."

How can I accomplish this in DB2 for iSeries 5.2?

Thanks!
0
Comment
Question by:astankovic
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 8

Expert Comment

by:nagki
ID: 13710102
Hi astankovic,

UPDATE tbl1 SET fld1 = 'value'
WHERE fld2 IN
     (
          SELECT fld2
          FROM (select fld2,row_number() over() as rn from tbl1 WHERE fld3 = '' ) as x
          where rn < 100)
         


Cheers!
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 13711163

Your original SQL isn't far off, though version 5.2 may be too old to support the FETCH FIRST clause.


UPDATE tbl1 SET fld1 = 'value'
WHERE fld2 IN
(
  SELECT fld2
  FROM tbl1
  ORDER BY fld2 DESC
  FETCH FIRST 100 ROWS ONLY
)


Good Luck,
Kent
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 13712002
My 5.1 system supports FETCH FIRST n ROWS, so 5.2 should definitely do it.

HTH,
DaveSlash
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 13712208

Cool.  Then to correct my typo:

UPDATE tbl1 SET fld1 = 'value'
WHERE fld2 IN
(
  SELECT fld3
  FROM tbl1
  WHERE fld4 = ''
  ORDER BY fld3 DESC
  FETCH FIRST 100 ROWS ONLY
)

Should work fine.  I use that technique a lot.

Kent
0
 
LVL 4

Author Comment

by:astankovic
ID: 13718323
Kdo,

That's basically the same as I had other than the addition of ORDER BY clause.
But that still doesn't work, I get "Keyword ORDER not expected..." error.

FETCH FIRST n ROWS ONLY works in a simple query, but it doesn't seem to work in the subquery. The same thing about ORDER BY,

Any connection string options that I need to set to enable that? I'm connecting with Microsoft .NET OleDb provider.

Thanks,
Alex
0
 
LVL 4

Author Comment

by:astankovic
ID: 13718341
nagki ,

That errors out too.
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 13718431
Just for kicks, try removing the ORDER BY and the FETCH FIRST clauses.  Does it then work?

Alternately, you could use a WITH clause.  Try something like this:

With tempTable as (
  SELECT fld3
  FROM tbl1
  WHERE fld4 = ''
  ORDER BY fld3 DESC
  FETCH FIRST 100 ROWS ONLY
)
UPDATE tbl1 SET fld1 = 'value'
WHERE fld2 IN
    (select fld3 from tempTable)

HTH,
DaveSlash
0
 
LVL 18

Accepted Solution

by:
Dave Ford earned 2000 total points
ID: 13718449
OK. I just tried it.

The only way I could get it to work was to remove the ORDER BY and the FETCH FIRST clauses.

Hmmm, I'm sure there's an explanantion for that, but it does seem weird to me.

DaveSlash
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 13720938

Strange....

I have no problem with queries that use that syntax.

I wonder if they require OLAP or other extensions?

0
 
LVL 8

Expert Comment

by:nagki
ID: 13731544
Hi astankovic,
can u post what error my query is giving as it worked fine for me

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month11 days, 11 hours left to enroll

564 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