INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Problem on DB2 Update Table

Problem on DB2 Update Table

(OP)
1.) csvtest table
CREATE TABLE csvtest (intcode varCHAR(14), deptcode varchar(3), alphacode varchar(5))
sample record of csvtest :
intcode ----- deptcode ----- alphacode
12021907-------13----------- "DSP"
11992564-------14----------- "ALP"
11803570-------15----------- "TOS"

2.) itemtest table
CREATE TABLE itemTest (intcode CHAR(14), alpha_code varchar(3))
sample record of itemtest :
intcode-------------------- alpha_code
00001202190713 ------------ ASL
00001199256408 ------------ ALS
00001180357015 ------------ DSP

the composition of intcode 00001202190713 is
Code- skucode - deptcode
0000- 12021907 - 13

I want to replace all alpha_code of itemtest table with the alphacode of csvtest table.
skucode and deptcode should match with intcode and deptcode of csvtest table..

Here is my sample code. Can anyone help me please... thank you


Imports IBM.Data.DB2
Imports System.IO
Imports System.IO.File
Imports IBM.Data.DB2.DB2BulkCopy

Dim con As New DB2Connection(GlobalVariables.masterconnection)

Dim COM As String = "Update itemTEST b set b.alpha_code = (select a.alphacode from csvtest a where substr(b.intcode,4,8)=a.intcode)"

Dim Adpt As New DB2DataAdapter(com, con)

Update itemTEST b set b.alpha_code = a.alpha_code
from csvtest a
where substr(b.intcode,4,8)=a.intcode

RE: Problem on DB2 Update Table

so you have
csvtest

CODE

INTCODE         DEPTCODE  ALPHACODE
12021907          13        DSP    
11992564          14        ALP    
11803570          15        TOS 
itemtest

CODE

INTCODE         ALPHA_CODE
00001202190713     ASL    
00001199256408     ALS    
00001180357015     DSP 

then after updatimg with

CODE

update itemtest as a
  set a.alpha_code = (select alphacode from csvtest  as b 
      where substr(a.intcode, 5) = b.intcode || b.deptcode)
  where 
    substr(a.intcode, 5) in 
      (select intcode || deptcode from csvtest) 
we get
itemtest

CODE

INTCODE         ALPHA_CODE
00001202190713     DSP    
00001199256408     ALS    
00001180357015     TOS 

Or if you want to ignore csvtest.deptcode as key in itemtest.intcode, you can update like this

CODE

update itemtest as a
  set a.alpha_code = (select alphacode from csvtest  as b 
      where substr(a.intcode, 5, 8) = b.intcode)
  where 
    substr(a.intcode, 5, 8) in 
      (select intcode from csvtest)
; 
an you will get
itemtest

CODE

INTCODE         ALPHA_CODE
00001202190713     DSP    
00001199256408     ALP    
00001180357015     TOS 


RE: Problem on DB2 Update Table

(OP)
Thank you so much Mikrom...

RE: Problem on DB2 Update Table

(OP)
Things are getting better now amazing...

One more.. hehe

I need a select statement that will only display a record with incorrect alpha_code in itemtest table.
The Select statement will be executed prior to Update.. Thanks

sample result should be:

INTCODE -----------ALPHA_CODE
00001202190713-----ASL
00001180357015-----DSP

RE: Problem on DB2 Update Table

Quote (Rod41)


I need a select statement that will only display a record with incorrect alpha_code in itemtest table.

For example this

CODE

select * from 
  itemtest as a inner join csvtest  as b on
  substr(a.intcode, 5) = b.intcode || b.deptcode
where a.alpha_code != b.alphacode 

Result for data given above:

CODE

INTCODE         ALPHA_CODE  INTCODE         DEPTCODE  ALPHACODE
00001202190713     ASL      12021907          13        DSP    
00001180357015     DSP      11803570          15        TOS 

RE: Problem on DB2 Update Table

(OP)
fabulous... perfect!

Thank you...

RE: Problem on DB2 Update Table

You are welcome

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close