SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Update from select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jgoodstein
Starting Member

3 Posts

Posted - 07/27/2005 :  09:43:41  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 07/27/2005 :  09:50:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 07/27/2005 :  10:57:53  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 07/27/2005 :  11:00:21  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 07/27/2005 :  11:13:24  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 07/28/2005 :  00:54:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Canada
1 Posts

Posted - 08/06/2005 :  23:24:55  Show Profile  Visit Ron Sorrell's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000