Complex update - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Complex update

Author

Topic

Scrappy
Starting Member

3 Posts

Posted - 2007-04-03 : 11:38:19

1) Is this possible?

I need to update a field with the sum of another field from a seperate table.

I need to update PROCESS_DATA.REJECTED for each unique PROCESS_ID with the sum of PRCS_DATA_SMPL.ELEMENT_NO = 10, PRCS_DATA_SMPL.ELEMENT_NO = 20, PRCS_DATA_SMPL.ELEMENT_NO = 30, PRCS_DATA_SMPL.ELEMENT_NO = 40, and PRCS_DATA_SMPL.ELEMENT_NO = 50

2)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PROCESS_DATA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PROCESS_DATA]
GO

CREATE TABLE [dbo].[PROCESS_DATA] (
[PROCESS_DATA_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PROCESS_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OPERATION_NO] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COLLECT_DATE] [datetime] NOT NULL ,
[PRODUCT_ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PO_NUMBER] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOT_NUMBER] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOT_SIZE] [numeric](10, 0) NULL ,
[ACCEPTED] [numeric](10, 0) NULL ,
[REJECTED] [numeric](10, 0) NULL ,
[ASSIGNED_TO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FUNCTION_CODE] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JOB_CODE] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DUE_DATE] [datetime] NULL ,
[MADEBY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MADEBY_EMPLOYEE_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MADEBY_SUPPLIER_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MADEBY_CUSTOMER_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CHECKEDBY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CHECKEDBY_CUS_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CHECKEDBY_SUP_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CHECKEDBY_EMP_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EQUIPMENT_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TOLLING_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USER_DEFINED_1] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLOSED] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATE_BY] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREATE_DATE] [datetime] NOT NULL ,
[MODIFIED_BY] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MODIFIED_DATE] [datetime] NULL ,
[PLAN_REV_LEVEL] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PLAN_REV_DATE] [datetime] NULL ,
[ENTITY_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PROCESS_DATA] ADD
CONSTRAINT [DEFAULT__I_80] DEFAULT ('I') FOR [MADEBY],
CONSTRAINT [DEFAULT__I_81] DEFAULT ('I') FOR [CHECKEDBY],
CONSTRAINT [DEFAULT__N_32] DEFAULT ('N') FOR [CLOSED],
CONSTRAINT [PK_PROCESS_DATA] PRIMARY KEY NONCLUSTERED
(
[PROCESS_DATA_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_PS_DATA_1] CHECK ([MADEBY] = 'I' or [MADEBY] = 'S' or [MADEBY] = 'C'),
CONSTRAINT [CK_PS_DATA_2] CHECK ([CHECKEDBY] = 'I' or [CHECKEDBY] = 'S' or [CHECKEDBY] = 'C'),
CONSTRAINT [CK_PS_DATA_3] CHECK ([CLOSED] = 'N' or [CLOSED] = 'Y')
GO

CREATE INDEX [IX_PRCDAT_PRCID] ON [dbo].[PROCESS_DATA]([PROCESS_ID], [OPERATION_NO]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_PRCDAT_COLDT] ON [dbo].[PROCESS_DATA]([COLLECT_DATE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PRCS_DATA_SMPL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PRCS_DATA_SMPL]
GO

CREATE TABLE [dbo].[PRCS_DATA_SMPL] (
[PROCESS_DATA_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PROCESS_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AREA_NUMBER] [numeric](5, 0) NOT NULL ,
[ACTIVITY_NO] [numeric](5, 0) NOT NULL ,
[ELEMENT_NO] [numeric](5, 0) NOT NULL ,
[OPERATION_NO] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SAMPLE_NUMBER] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SERIAL_NUMBER] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VARIABLE_DATA] [numeric](13, 6) NULL ,
[ATTRIBUTE_DATA] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TEXT_DATA] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEVICE_ID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POST_NONCONFORM] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CAUSE_CODE] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ATTRIBUTES] [smallint] NOT NULL ,
[WS_REVISION_LEVEL] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WS_ISSUE_DATE] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PRCS_DATA_SMPL] WITH NOCHECK ADD
CONSTRAINT [PK_PS_DATA_SAMPLE] PRIMARY KEY CLUSTERED
(
[PROCESS_DATA_ID],
[PROCESS_ID],
[AREA_NUMBER],
[ACTIVITY_NO],
[ELEMENT_NO],
[SAMPLE_NUMBER]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[PRCS_DATA_SMPL] ADD
CONSTRAINT [DF_PS_DATA_SMPL1] DEFAULT ('N') FOR [POST_NONCONFORM],
CONSTRAINT [DF__PRCS_DATA__ATTRI__345F90D4] DEFAULT (0) FOR [ATTRIBUTES],
CONSTRAINT [CK_PS_DATA_SMPL] CHECK ([POST_NONCONFORM] = 'Y' or [POST_NONCONFORM] = 'N')
GO

3) Sample Data:
PROCESS_DATA_ID PROCESS_ID,OPERATION_NO,PRODUCT_ID,LOT_NUMBER,LOT_SIZE,ACCEPTED,REJECTED,ACITIVY_NO,ELEMENT_NO,TEXT_DATA
11 SORT RUN 23672001 6 4953 4914 0 20 10 0
11 SORT RUN 23672001 6 4953 4914 0 20 20 9
11 SORT RUN 23672001 6 4953 4914 0 20 30 10
11 SORT RUN 23672001 6 4953 4914 0 20 40 0
11 SORT RUN 23672001 6 4953 4914 0 20 50 20

4)Here is the select statement that gives me all the information I need:

SELECT *, PROCESS_DATA.PROCESS_ID AS Expr1, PROCESS_DATA.OPERATION_NO AS Expr2, PRCS_DATA_SMPL.TEXT_DATA AS Expr5,
PRCS_DATA_SMPL.ACTIVITY_NO AS Expr3, PRCS_DATA_SMPL.ELEMENT_NO AS Expr4
FROM PROCESS_DATA INNER JOIN
PRCS_DATA_SMPL ON PROCESS_DATA.PROCESS_DATA_ID = PRCS_DATA_SMPL.PROCESS_DATA_ID
WHERE (PROCESS_DATA.OPERATION_NO = 'Run') AND (PROCESS_DATA.PROCESS_ID = 'SORT') AND (PRCS_DATA_SMPL.ACTIVITY_NO = 20) AND
(PRCS_DATA_SMPL.ELEMENT_NO = 10 OR
PRCS_DATA_SMPL.ELEMENT_NO = 20 OR
PRCS_DATA_SMPL.ELEMENT_NO = 30 OR
PRCS_DATA_SMPL.ELEMENT_NO = 40 OR
PRCS_DATA_SMPL.ELEMENT_NO = 50)

5) Here is what I would like it to look like:
PROCESS_DATA_ID PROCESS_ID,OPERATION_NO,PRODUCT_ID,LOT_NUMBER,LOT_SIZE,ACCEPTED,REJECTED,ACITIVY_NO,ELEMENT_NO,TEXT_DATA
11 SORT RUN 23672001 6 4953 4914 39 20 10 0
11 SORT RUN 23672001 6 4953 4914 39 20 20 9
11 SORT RUN 23672001 6 4953 4914 39 20 30 10
11 SORT RUN 23672001 6 4953 4914 39 20 40 0
11 SORT RUN 23672001 6 4953 4914 39 20 50 20

Any help is appreciated, thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-03 : 11:41:57

Clear as mud....first of all, as soon as you do this, thesaved derived data wil be stale...I wouldn't reccomend it, but if you like, read the hint link in my sig and post what it asks you...you should get an answer quickly that way

Even though you shouldn't do it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-04-03 : 11:52:34

"I need to update a field with the sum of another field from a seperate table. "

Sounds like you need a computed column to me.

You dont store that info. you return it on the fly.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Scrappy
Starting Member

3 Posts

Posted - 2007-04-03 : 12:53:37

quote:
Originally posted by DonAtWork

"I need to update a field with the sum of another field from a seperate table. "

Sounds like you need a computed column to me.
You dont store that info. you return it on the fly.

this is a database used by an ERP. The data entry user left a field blank (REJECTS). But happened to fill out 5 user defined fields (They are not actually 5 seperate fields the field is TEXT_DATA and the 5 categories are defined by ELEMENT_NO 10 thru 50) that break down the rejects into 5 categories. I need to sum the 5 different categories of rejects and update that into the REJECT field by PROCESS_ID.

I hope that makes sense. I will update my first post with the required info.

Scrappy
Starting Member

3 Posts

Posted - 2007-04-03 : 14:11:36

Updated my original post

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources