Update Statment

JDionne · #1 (**permalink**) 05-25-04, 10:00

I am running DB2 8.1 fixpack 5 on a windows 2003 enterprise server.
I am in the middle of a conversoin from SQL Server to DB2 and I need to rewrite some code.
The original code is as follows:

Code:

update dbo.stg_joc_tbl 
set office = s.office
from dbo.stg_joc_tbl j, dbo.REF_NA_SALES_XREF_TBL s
where j.district = s.district_code

I have found that i cant use the from clause in an update statment for DB2. I have tried various forms of subselect statments with no avail. any help would be great.
Jim

sathyaram_s · #2 (**permalink**) 05-25-04, 10:32

Check for the syntax ... But, basically, I assume this will work

update dbo.stg_joc_tbl j set office=(select s.office from dbo.REF_NA_SALES_XREF_TBL s where j.district=s.distict) and
exists (
select 1 from dbo.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district
)

JDionne · #3 (**permalink**) 05-25-04, 10:46

Quote:

Originally Posted by sathyaram_s

Check for the syntax ... But, basically, I assume this will work

update dbo.stg_joc_tbl j set office=(select s.office from dbo.REF_NA_SALES_XREF_TBL s where j.district=s.distict) and
exists (
select 1 from dbo.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district
)

Your code:

Code:

update dbo.stg_joc_tbl j set office=(select s.office from dbo.REF_NA_SALES_XREF_TBL s where j.district=s.distict) and 
exists (
select 1 from dbo.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district
)

Produced the following error:

Code:

update db2admin.stg_joc_tbl j 
Set office=(select s.office from db2admin.REF_NA_SALES_XREF_TBL s where j.district=s.distict) and 
exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district);

then i thought that the parinthases were in the wrong spot so i ran the following code:

Code:

update db2admin.stg_joc_tbl j 
Set office=(select s.office from db2admin.REF_NA_SALES_XREF_TBL s where j.district=s.distict and 
exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district));

and got the following error

Code:

update db2admin.stg_joc_tbl j 
Set office=(select s.office from db2admin.REF_NA_SALES_XREF_TBL s where j.district=s.distict and 
exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district));

I have looked at the syntax in the documentation, but i havent seen anything that deals with anything this complicated. If you see it documented I would be happy to go read it if you tell me the chaperter/heading.
thanks for all of your help
Jim

JDionne · #4 (**permalink**) 05-25-04, 10:51

hmm maybe i should be looking at the code beter. I didnt notice a few column names named improperly.

Code:

update db2admin.stg_joc_tbl j 
Set office=(select s.office from db2admin.REF_NA_SALES_XREF_TBL s where j.district=s.district_code and 
exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.district=s1.district_code));

This is running as we speak
Thanks again for the help
Jim

dsusendran · #5 (**permalink**) 06-01-04, 14:11

Quote:

Originally Posted by JDionne

hmm maybe i should be looking at the code beter. I didnt notice a few column names named improperly.

Code:

update db2admin.stg_joc_tbl j 
Set office=(select s.office from db2admin.REF_NA_SALES_XREF_TBL s where j.district=s.district_code and 
exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.district=s1.district_code));

This is running as we speak
Thanks again for the help
Jim

Jim,

I am trying to do the same thing you tried sometime ago. Were you able to do a mass update?

Thanks in advance,

Newbie

JDionne · #6 (**permalink**) 06-01-04, 14:20

Quote:

Originally Posted by dsusendran

Jim,

I am trying to do the same thing you tried sometime ago. Were you able to do a mass update?

Thanks in advance,

Newbie

This bit of code does the job for me

Code:

update db2admin.stg_joc_tbl j 
Set office=(select s.office from db2admin.REF_NA_SALES_XREF_TBL s where j.district=s.district_code and 
exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.district=s1.district_code));

Honestly Im still working on firguring out exactly what is going on here.
I would go to this reference for a deeper understanding of the code.
http://ourworld.compuserve.com/homep...l/DB2V81CK.PDF

sathyaram_s · #7 (**permalink**) 01-18-06, 13:33

Better late then never

The update should have been

update dbo.stg_joc_tbl j set office=(select s.office from dbo.REF_NA_SALES_XREF_TBL s where j.district=s.distict) where
exists (
select 1 from dbo.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district
)

Quote:

Originally Posted by sathyaram_s

Check for the syntax ... But, basically, I assume this will work

update dbo.stg_joc_tbl j set office=(select s.office from dbo.REF_NA_SALES_XREF_TBL s where j.district=s.distict) and
exists (
select 1 from dbo.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district
)

Internet Services

Web Development

Digital Marketing

Consumer Tech