Problem on DB2 Update Table

[Rod41]

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

 

[mikrom]

so you have
csvtest

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

itemtest

INTCODE         ALPHA_CODE
00001202190713     ASL    
00001199256408     ALS    
00001180357015     DSP

then after updatimg with

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

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

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

INTCODE         ALPHA_CODE
00001202190713     DSP    
00001199256408     ALP    
00001180357015     TOS

 

[Rod41]

Thank you so much Mikrom...

 

[Rod41]

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

 

[mikrom]

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

For example this

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:

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

 

[Rod41]

fabulous... perfect!

Thank you...

 

[mikrom]

You are welcome