SQL Server Forums - Update from select

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

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

Topic

jgoodstein
Starting Member

3 Posts

Posted - 07/27/2005 : 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

India
22461 Posts

Posted - 07/27/2005 : 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

jgoodstein
Starting Member

3 Posts

Posted - 07/27/2005 : 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

madhivanan
Premature Yak Congratulator

India
22461 Posts

Posted - 07/27/2005 : 11:00:21

Can you post the table structures

Madhivanan

Failing to plan is Planning to fail

jgoodstein
Starting Member

3 Posts

Posted - 07/27/2005 : 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?

madhivanan
Premature Yak Congratulator

India
22461 Posts

Posted - 07/28/2005 : 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

Ron Sorrell
Starting Member

Canada
1 Posts

Posted - 08/06/2005 : 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

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.06 seconds.