473,321 Members | 1,669 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Update multiple records

Hi, I have a weird UPDATE sequence I want to perform. The customer
does not want to use INSERT and DELETE. My issue is that there are
multiple Mat_Class, but I do not wish to hard code each one
individually. Originally I was thinking of using a select statement
and creating a loop to sub in each Mat_Class but I really have no idea
how to make that work purely in SQL. This is going to be run as a DTS
procedure overnight. Any help would be appreciated.

UPDATE dbo.ConformDaily
SET Pct_in= (select Pct_In from
v_R6_Skid_Chill_Conformance_last_7_days where Mat_Class=1 and
yyyymmdd='20040503') ,
Std_Dev= (select Std_Dev from v_R6_Skid_Chill_Conformance_last_7_days
where Mat_Class=1 and yyyymmdd='20040503' )
WHERE RecordType='SKIDCHILL' and
yyyymmdd='20040503' and Mat_Class=1

Next Update statement would be for Mat_Class=2, 3 etc. but they change
from day to day with values possible from 1-8.
Jul 20 '05 #1
4 8945
Mark S. (sm*********@yahoo.com) writes:
Hi, I have a weird UPDATE sequence I want to perform. The customer
does not want to use INSERT and DELETE. My issue is that there are
multiple Mat_Class, but I do not wish to hard code each one
individually. Originally I was thinking of using a select statement
and creating a loop to sub in each Mat_Class but I really have no idea
how to make that work purely in SQL. This is going to be run as a DTS
procedure overnight. Any help would be appreciated.

UPDATE dbo.ConformDaily
SET Pct_in= (select Pct_In from
v_R6_Skid_Chill_Conformance_last_7_days where Mat_Class=1 and
yyyymmdd='20040503') ,
Std_Dev= (select Std_Dev from v_R6_Skid_Chill_Conformance_last_7_days
where Mat_Class=1 and yyyymmdd='20040503' )
WHERE RecordType='SKIDCHILL' and
yyyymmdd='20040503' and Mat_Class=1

Next Update statement would be for Mat_Class=2, 3 etc. but they change
from day to day with values possible from 1-8.


Well, you need to explain more. You know your tables - we don't.

The normal advice for this sort of question is that you post:
o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired result given the sample data.

The point that you post your table and data as SQL statements is that
then one can copy and paste into Query Analyzer to test a solution.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
CREATE TABLE [dbo].[ConformDaily] (
[ident] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[RecordType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[yyyymmdd] [int] NULL ,
[Mat_Class] [int] NULL ,
[pct_in] [float] NULL ,
[std_dev] [float] NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[SkidChill] (
[ident] [int] IDENTITY (1, 1) NOT NULL ,
[yyyymmdd] [int] NULL ,
[pct_in] [float] NULL ,
[std_dev] [float] NULL ,
[Mat_Class] [int] NULL ,
[TRANS_AIM] [float] NULL ,
[TRANS_OFFSET] [float] NULL ,
[TRANS_CALC] [float] NULL ,
[TRANS_CROWN] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[HRP_UP] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HRP_DOWN] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HRP_AUTO] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HRP_USED] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CS_TEMP_INIT_HEAD] [varchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CS_TEMP_FINAL_HEAD] [varchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CS_TEMP_TAIL] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CROP_HEAD_LEN] [float] NULL,
[UR_LEAD_SPEED] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PR_LEAD_SPEED] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PR_LOAD] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COILER_AUTO] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MANDREL_LEAD_SPEED] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MANDREL_AMPS] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COILER_GUIDE_SU] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COILER_GUIDE_OFFSET] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PR_GAP_SETUP] [float] NULL ,
[PR_GAP_OFFSET] [float] NULL ,
[COILER_USED] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MANDREL_REF_AT_PU] [varchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MANDREL_SPD_AT_PU] [varchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PR_SPD_AT_PU] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MANDREL_LEAD_SPD_REF] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PR_LEAD_SPD_REF] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[UR_HOLD_TIME] [float] NULL ,
[PCNT_CARBON] [float] NULL ,
[PR_SPEED_ERR] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COIL_TEMP_AIM] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OPER_COIL_TEMP_OFFSET] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[COIL_TEMP_AIM_FMSU] [varchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CT_PLUS_LIMIT] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CT_PLUS_LIMIT_2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CT_MINUS_LIMT] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CT_MINUS_LIMIT_2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COIL_TEMP_HE_ERR] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CT_MIN_TOL_1] [float] NULL

) ON [PRIMARY]

GO
CREATE VIEW dbo.v_R6_Skid_Chill_Conformance_last_7_days
AS SELECT yyyymmdd, Mat_Class, pct_in, std_dev
from SkidChill
GO
Insert INTO ConformDaily(RecordType, yyyymmdd, Mat_Class, Pct_in,
Std_Dev)
VALUES ('SkidChill', 20040503, 1, 0, 0)
Insert INTO ConformDaily(RecordType, yyyymmdd, Mat_Class, Pct_in,
Std_Dev)
VALUES ('SkidChill', 20040503, 2, 0, 0)
Insert INTO ConformDaily(RecordType, yyyymmdd, Mat_Class, Pct_in,
Std_Dev)
VALUES ('SkidChill', 20040503, 5, 0, 0)

Insert INTO SkidChill(yyyymmdd, Mat_Class, Pct_in, Std_Dev)
VALUES (20040503, 1, 97.1, 26.98)
Insert INTO SkidChill(yyyymmdd, Mat_Class, Pct_in, Std_Dev)
VALUES (20040503, 2, 95.894, 29.23)
Insert INTO SkidChill(yyyymmdd, Mat_Class, Pct_in, Std_Dev)
VALUES (20040503, 5, 92.123, 28.12)
UPDATE dbo.ConformDaily
SET Pct_in= (select Pct_In from
v_R6_Skid_Chill_Conformance_last_7_days where Mat_Class=1 and
yyyymmdd='20040503') ,
Std_Dev= (select Std_Dev from v_R6_Skid_Chill_Conformance_last_7_days
where Mat_Class=1 and yyyymmdd='20040503' )
WHERE RecordType='SKIDCHILL' and
yyyymmdd='20040503' and Mat_Class=1

UPDATE dbo.ConformDaily
SET Pct_in= (select Pct_In from
v_R6_Skid_Chill_Conformance_last_7_days where Mat_Class=2 and
yyyymmdd='20040503') ,
Std_Dev= (select Std_Dev from v_R6_Skid_Chill_Conformance_last_7_days
where Mat_Class=2 and yyyymmdd='20040503' )
WHERE RecordType='SKIDCHILL' and
yyyymmdd='20040503' and Mat_Class=2

UPDATE dbo.ConformDaily
SET Pct_in= (select Pct_In from
v_R6_Skid_Chill_Conformance_last_7_days where Mat_Class=5 and
yyyymmdd='20040503') ,
Std_Dev= (select Std_Dev from v_R6_Skid_Chill_Conformance_last_7_days
where Mat_Class=5 and yyyymmdd='20040503' )
WHERE RecordType='SKIDCHILL' and
yyyymmdd='20040503' and Mat_Class=5
/*

Results Desired
ConformDaily Table
yyyymmdd Mat_Class pct_in std_dev
20040503 1 97.1 26.98
20040503 2 95.894 29.23
20040503 5 92.123 28.12
The ConformDaily will now have the updated records from the view
Basically the view is more real time, and the ConformDaily Table is
being used
as the source to generate reports that do not have to be in real time,
but need to load and generate quickly
So, I want to use an update command, not a delete and insert.
Is there any way to loop through all available Mat_Class and update
the records? I will not
know which material classes they produce each day.
Hope that helps, any more info needed, please ask!

*/
Jul 20 '05 #3
On 5 May 2004 07:04:02 -0700, Mark S. wrote:

(snip DDL and sample data)

Hi Mark,

Thanks for providing DDL and sample data as INSERTs. It made testing
the following solution a breeze!
Results Desired
ConformDaily Table
yyyymmdd Mat_Class pct_in std_dev
20040503 1 97.1 26.98
20040503 2 95.894 29.23
20040503 5 92.123 28.12
The ConformDaily will now have the updated records from the view


The following query produces the above results:

UPDATE dbo.ConformDaily
SET Pct_in= (select Pct_in
from v_R6_Skid_Chill_Conformance_last_7_days
where Mat_Class=ConformDaily.Mat_Class
and yyyymmdd=ConformDaily.yyyymmdd) ,
Std_Dev= (select Std_Dev
from v_R6_Skid_Chill_Conformance_last_7_days
where Mat_Class=ConformDaily.Mat_Class
and yyyymmdd=ConformDaily.yyyymmdd)
WHERE RecordType='SKIDCHILL'
and yyyymmdd='20040503'
/* and Mat_Class IN (1, 2, 5) */
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #4
Most Excellent Hugo!
Worked like a charm!
Thanks for the help!
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: R Duncan | last post by:
Hi, I'm considering moving a payroll application out of Microsoft Access to some web-based solution. It is getting way to big for Access and the system is growing unstable, so I'm learning PHP...
1
by: Roy Adams | last post by:
Hello people I've recently been woring with multiple insert from text fields and got that woking fine thanks to the help from people from this forum now i'm trying to deal with multiple update,...
7
by: Drew | last post by:
I have a db table like the following, UID, int auto-increment RegNo Person Relation YearsKnown Now here is some sample data from this table,
1
by: Wolffang | last post by:
From: "Wolffang" <javid@techlite.co.uk> Subject: How to do a multiple select and update in a datagrid Date: 23 October 2004 21:28 Using Visual studio.net VB I have a datagrid that brings...
1
by: Kenneth | last post by:
Dear all, How can I update multiple records in using ASP.NET? Currently I have a table which require to update frequently but I can update records one by one using DataGrid. Kenneth
11
by: Siv | last post by:
Hi, I seem to be having a problem with a DataAdapter against an Access database. My app deletes 3 records runs a da.update(dt) where dt is a data.Datatable. I then proceed to update a list to...
10
by: Roger Withnell | last post by:
I'm using ASP, VBScript and SQL Server. I'm also using UTF-8 character set and so my codepage is 65001 and SQL Server datatype nvarchar. I can insert unicode characters correctly into the...
1
by: Hexman | last post by:
Hello All, What I'm trying to do is update a child record using a parent-child relation. I want to find out if it is faster than than doing multiple selects. Anyways, I've created a dataset...
0
by: Access Programming only with macros, no code | last post by:
ERROR MESSAGE: Could not update; currently locked by another session on this machine. BACKGROUND I have the following objects: Table1 - HO (which has about 51,000+ records) Table2 -...
4
by: phill86 | last post by:
Hi, i have a form that runs a query in a recordset on the after update method if i copy and paste one record at a time the query picks up the records in the underlying table but if i paste...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.