MVSFORUMS.com

js01

Hi,

I am trying to update 2000 products thru running a query thru JCL
and givenig as file input in where clause ,
i want to know that not updated products (not found in database)
is teher any possibiliy

ex:

UPDATE PRODUCT
SET QTY = 100
WHERE PRODUCTid IN ( 10,11,12,16,17)

product id 10,12,16 got found in database ,
11 and 17 are not found

i would like to get a list of not found ( 11,17)

please help me.

thank you

shekar123 · Posted: Tue Aug 29, 2006 2:47 pm Post subject:

js01,

You can use the query below to know all avaialble PRODUCTID in the table , the result will give you entire list .

kolusu · Posted: Tue Aug 29, 2006 2:49 pm Post subject:

js01 · Posted: Tue Aug 29, 2006 4:23 pm Post subject:

Kolusu,
So , it is not possible ,am i right?

i think i will have to write a program.

thank you

shekar123 · Posted: Wed Aug 30, 2006 1:01 am Post subject:

js01,

Code a program ! what for .If u read the answers to the post you can easily make that if the PRODUCTID is not present in the table itself how are you going to get the PRODUCTID.

Please understand the answers to the post.
_________________
Shekar
Grow Technically

firoze · Beginner Joined: 25 Aug 2006 Posts: 2 Topics: 0

Most of the systems I worked , All the tables contains columns for update time stamp and update user id.In your case you can execute a query to see find out which are not updated.

Another method , you run a select query before running the update query to identify non matching rows.

kolusu · Posted: Wed Aug 30, 2006 7:27 am Post subject:

js01 · Posted: Wed Aug 30, 2006 11:26 pm Post subject:

all,

I think i may confusing you....

please leave about the update...lets take a select statement

it may be stupid question ,but i usally get this type of requirement ,
i used to write a porgram (read one by one from input file and move this productid to hostvariable and run a select query ,if error code = 0 then next sentence
else (sqlerror=100) display 'not found' and write into ouput file)

but i want to kow if it possible thru sql .

ex:

product table

---------------------------
prodname | qty
--------------------------
ipod | 10
notebook | 20
camera | 40
---------------------

below is the slec query

select * from product
where prodname in ('ipod','notbbok','pendrive')

reslut will be:

ipod | 10
notebook | 20

so that means 'pendrive' is not in db , i would like to display it in resultset like below

ipod | 10
notebook | 20
pendrive | not found

we can use join with temp table.

but i dont want to do as i get arround 900K products and takes more time.

Please advise and let me know if it is not clear.

thank you