Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Update from select

Author  Topic 

jgoodstein
Starting Member

3 Posts
Posted - 2005-07-27 : 09:43:41
I have two tables in Access(or SQL server) that have identical info. one table has the dept# and one does not. I am trying to write the update to the one that does not but am failing miserably. I am desperate at this point to get it to work heres what i have tried and has failed:

UPDATE CustomersNID set CustomersNID.DEPT = (SELECT [_CUSTOMER_].DEPT FROM [_CUSTOMER_] where CustomersNID.CLIENT = [_CUSTOMER_].CLIENT) ;

the client info is the same BTW

this one also failed

UPDATE CustomersNID set CustomersNID.DEPT = (SELECT [_CUSTOMER_].DEPT
FROM CustomersNID INNER JOIN _CUSTOMER_ ON CustomersNID.CLIENT = [_CUSTOMER_].CLIENT);

thank you in advance for any of your help

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-07-27 : 09:50:27
Update C set C.DEPT=Cu.DEPT from CustomersNID C inner join [_CUSTOMER_] Cu
on C.CLIENT=Cu.Client


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jgoodstein
Starting Member

3 Posts
Posted - 2005-07-27 : 10:57:53
That worked for the most part in my test tables but once i try to run it on the real tables i get this error:

Server: Msg 50000, Level 18, State 1, Procedure SMSYSERROR, Line 10
Magic Error: Parent key not found for value in column "DEPT" in table "_CUSTOMER_"

This is the final command i ran:

Update C set C.DEPT=Cu.DEPT from _SMDBA_._CUSTOMER_ C inner join customers2 Cu
on C.CLIENT=Cu.Client
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-07-27 : 11:00:21
Can you post the table structures

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jgoodstein
Starting Member

3 Posts
Posted - 2005-07-27 : 11:13:24
CREATE TABLE [dbo].[customers2] (
[SEQUENCE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTMODIFIED] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTUSER] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[_GROUP_] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[_OWNER_] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[_OWNERPERMS_] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLIENT] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FNAME] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MNAME] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NAME] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEPT] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BILL TO] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MAIL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BLDNG] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OFFICE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMPANY] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[_EMAILID_] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POSITION] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BEEPER] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAX] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PICTURE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TIME_ZONE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POSTAL_CODE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INTL_PHONE_.] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INTL_FAX_.] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COUNTRY] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCDT01] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCDT02] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCINT01] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCINT02] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCTXT01] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCTXT02] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCTXT03] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCTXT04] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCTXT05] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCTXT06] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSPWD] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SEQ_SEVERITY:] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USEDEPT:] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USECOMPANY:] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATEDFROMSSD:] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DISPLAYCLIENTCOMMENTS:] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[_INACTIVE_:] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LINK_MSCRM_CONTACTID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WINUSERID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SURVEY_COUNTER] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NUM_SURVEYS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LAST_SURVEYED] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SEQ_SURVEY] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO




CREATE TABLE [_SMDBA_].[_CUSTOMER_] (
[SEQUENCE] [int] NOT NULL ,
[LASTMODIFIED] [datetime] NOT NULL ,
[LASTUSER] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[_GROUP_] [int] NULL ,
[_OWNER_] [int] NULL ,
[_OWNERPERMS_] [int] NULL ,
[NOTE] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLIENT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FNAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MNAME] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONE] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEPT] [int] NULL ,
[BILL TO] [int] NULL ,
[MAIL] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BLDNG] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OFFICE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMPANY] [int] NULL ,
[_EMAILID_] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POSITION] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BEEPER] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAX] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PICTURE] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TIME_ZONE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POSTAL_CODE] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INTL_PHONE_#] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INTL_FAX_#] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COUNTRY] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCDT01] [datetime] NULL ,
[CLI_CCDT02] [datetime] NULL ,
[CLI_CCINT01] [int] NULL ,
[CLI_CCINT02] [int] NULL ,
[CLI_CCTXT01] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCTXT02] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCTXT03] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCTXT04] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCTXT05] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLI_CCTXT06] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSPWD] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SEQ_SEVERITY:] [int] NULL ,
[USEDEPT:] [smallint] NULL ,
[USECOMPANY:] [smallint] NULL ,
[CREATEDFROMSSD:] [smallint] NULL ,
[DISPLAYCLIENTCOMMENTS:] [smallint] NULL ,
[_INACTIVE_:] [smallint] NOT NULL ,
[LINK_MSCRM_CONTACTID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WINUSERID] [varchar] (85) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SURVEY_COUNTER] [int] NULL ,
[NUM_SURVEYS] [int] NULL ,
[LAST_SURVEYED] [datetime] NULL ,
[SEQ_SURVEY] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Is that what you wanted?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-07-28 : 00:54:31
I think the DEPT column may be null in [_CUSTOMER_] table. Make it to not null

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Ron Sorrell
Starting Member

1 Post
Posted - 2005-08-06 : 23:24:55
Did you get this resolved? I recognize the Magic Tables or equivalents. It mostly likely means your source data has extra department information existing in your test data but not in your real data. You will have to ensure the all the available departments are imported or populated into the _SMDBA_._DEPT_ table first. Call me if you have any questions about this.

Ron Sorrell
rsorrell@rjrinnovations.com
613-913-1540

RJR Innovations
A Certified BMC/Magic Consulting Partner
http://www.rjrinnovations.com
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site