Update Of Records From Other Tables

Sep 11, 2000

I am trying to update a field within one table with the values from another table. With the criteria that another field in each table are equal. What is the correct way to do this. My syntax is all wrong.

thanks
Jason

View 1 Replies


ADVERTISEMENT

I Want To Transfer ONLY New Records AND Update Any Modified Records From Oracle Into SQL Server Using DTS

Jul 23, 2005

I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.

View 2 Replies View Related

Insert Records From Foxpro Tables To SQL Server Tables

Apr 22, 2004

Hi,

Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:

1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.

I only know the following ways to import Foxpro data into SQL Server:

#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables

I'm thinking whether the following choices will be better than the current way:

1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2

Thank you for any suggestion.

View 2 Replies View Related

Trigger To Update One Record On Update Of All The Tables Of Database

Jan 3, 2005

hi!

I have a big problem. If anyone can help.

I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.

I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.

But i don't know exactly how to do the coding for this?

Is there any other way to do this?

can DBCC help to retrieve this info?

Please advise me how to do this.

Thanks in advance.

Vaibhav

View 10 Replies View Related

Track The Changes To Normalised Tables And Update The Denormalised Tables Depending On The Changes To Normalised Tables

Dec 7, 2006

We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process

View 3 Replies View Related

Update Records

Aug 15, 2007

Here is my question if anyone can help...

I have two tables

Table 1

EmpName
PolicyNumber

Table 2

EmpName
PolicyNumber
NewEmpName

I would like to update Table 1 with the data from Table 2. Here is my problem..Lets say that I have two records in Table 2 that have the same policyNumber but two different NewEmpNames, it only takes the first. In other words, a single policynumber can be moved to a New EmpName and then again later on to another NewEmpName adn even again if need be

Any help is greatly appreciated.

-Matt




View 1 Replies View Related

Update Child Records Help

Oct 5, 2004

Please help

I have table1 which has many unique ID numbers and table2 that has many records for each ID. some of the ID numbers in table1 have changed and I have created a translation table (table3) that links the old and new ID numbers.

What I need to do is some sort of update sql statement that updates all the records in table2 changing all the oldID numbers to the new ones using the translation table.

Table1 and table2 are not linked...can anyone help me with the sql statement

example

Table 1
IDNUM NAME
12345 Joe
12346 Mary
12347 David

Table2
IDNUM FIELD1
12345 hello
12345 goodbye
12346 hello
12347 goodbye
12346 hello
12346 goodbye

Table3
OLDID NEW ID
12345 54321
12347 74321

need to change the IDNUM in Table2 to 54321 where IDNUM = 12345 and same with 12347..Need to do this for many many IDs but not all.

Thanks very much

View 1 Replies View Related

Insert And Update Records

Feb 4, 2008

Good day to all, I am new here so i hope i am doing things correctly.

The Company i work for make coils of shaped wire and work a 6 - 6 shift pattern

I have a database that is updated from a data collection source (MS Access) at 06:00 every morning. This seems to be working ok, my problem is that most coils fit nicely into the 6 - 6 shift pattern, but some now and again drift over into the next shift. I have written a crystal report that picks up this data. at the moment the coils are put in the database as: [Coil Start Time], [Coil Finish Time], [Coil Start Weight], [Coil Finish Weight], etc.

I have written (been helped to write) a SQL statement that will do the following:

Step 1: If the Coil Finish time is greater than the shift end time, then set the shit end time to be coil end time and zero start and finish wheight.
Step 2: The original Coil record is duplicated and Coil Start time set to start time of shift, all other data left alone.

Example of code:

-->>

SELECT [Batch Name], [Batch Start], [Batch End], [Coil Start Weight], [Coil Finish Weight], [Product], [Shift], [Operator ID], [Works Order No]
FROM dbo.tblCoilData
WHERE (DATEPART(hour, [Batch Start]) >= 6 AND DATEPART(hour, [Batch End]) < 18) OR
((DATEPART(hour, [Batch Start]) < 6 OR
DATEPART(hour, [Batch Start]) >= 18) AND (DATEPART(hour, [Batch End]) < 6 OR
DATEPART(hour, [Batch End]) >= 18))
UNION ALL
SELECT [Batch Name], [Batch Start], DATEADD(hour, 17, DATEADD(minute, 59, CONVERT(char(10), [Batch End], 101))), 0, 0, [Product], [Shift], [Operator ID],
[Works Order No]
FROM dbo.tblCoilData
WHERE DATEPART(hour, [Batch Start]) >= 6 AND DATEPART(hour, [Batch Start]) < 18 AND (DATEPART(hour, [Batch End]) < 6 OR
DATEPART(hour, [Batch End]) >= 18)
UNION ALL
SELECT [Batch Name], DATEADD(hour, 18, CONVERT(char(10), [Batch Start], 101)), [Batch End], [Coil Start Weight], [Coil Finish Weight], [Product], [Shift],
[Operator ID], [Works Order No]
FROM dbo.tblCoilData
WHERE DATEPART(hour, [Batch Start]) >= 6 AND DATEPART(hour, [Batch Start]) < 18 AND (DATEPART(hour, [Batch End]) < 6 OR
DATEPART(hour, [Batch End]) >= 18)
UNION ALL
SELECT [Batch Name], [Batch Start], DATEADD(hour, 5, DATEADD(minute, 59, CONVERT(char(10), [Batch End], 101))), 0, 0, [Product], [Shift], [Operator ID],
[Works Order No]
FROM dbo.tblCoilData
WHERE (DATEPART(hour, [Batch Start]) < 6 OR
DATEPART(hour, [Batch Start]) >= 18) AND DATEPART(hour, [Batch End]) >= 6 AND DATEPART(hour, [Batch End]) < 18
UNION ALL
SELECT [Batch Name], DATEADD(hour, 6, CONVERT(char(10), [Batch Start], 101)), [Batch End], [Coil Start Weight], [Coil Finish Weight], [Product], [Shift],
[Operator ID], [Works Order No]
FROM dbo.tblCoilData
WHERE (DATEPART(hour, [Batch Start]) < 6 OR
DATEPART(hour, [Batch Start]) >= 18) AND DATEPART(hour, [Batch End]) >= 6 AND DATEPART(hour, [Batch End]) < 18

<<--

I have 2 options now

option 1:
Leave this as a SQL View and report from this

option 2:
Insert updated records to the tblCoilData table so that the data in the table is permanent

I would prefer option 2 but am a bit of a nugget when it comes to writing update / insert statements, Could someone please help me with this

Thank you very kindly


Regards

Steve Dyson

View 4 Replies View Related

Update On Mulitple Records

Feb 17, 2004

I Have three tables

TASK
taskid, taskname, projectid, workid
1,,1,1
2,,1,2
3,,2,3

PROJECT
projectid, projectname
1, project1
2, project2
3, project3

WORK
workid, workname
1,work1
2,work2
3,work3

I need to do an update this way

Update the taskname as 'projectname' + '_' + 'workname' for any projectid.
projectname and workname coming from the projectid and workid in the task record

so the task table becomes
1,project1_work1,1,1
2,project1_work2,1,2
3,project2_work3,2,3

I can get all the records doing this

SELECT p.projectname+ ' ' + w.workname AS 'NEWNAME'
FROM task t
JOIN work w
ON t.workid = w.workid
JOIN project p
ON t.projectid = p.projectid
WHERE projectid = 2

how do i do an UPDATE?

any help is appreciated

View 2 Replies View Related

How To Update 2 Records On 1 Field

Aug 20, 2013

I have this code

I want to update nilai = total where kd_bulan = 9 and nilai = 0 where kd_bulan = 12

View 13 Replies View Related

Update Top 25 Records In Table

Sep 23, 2013

I'm having a problem with the syntax for doing an update to the top 25 records in my table.... This is what i have...

UPDATE TOP (25) FROM ud402.jd_mcp_master SET comments = 'MONDAY 092313 ' WHERE QUEUE_NAME = 'JD_Testing' ORDER BY DATE_WORKED ;

View 8 Replies View Related

What To Update The Duplicate Records

Jul 11, 2007

hi,

i want to update the second row of the c column.can any one help me in this . this is the sample records.
a b c
001testNULL
001testNULL
005testNULL

View 16 Replies View Related

Update Take Too Long On Some Records.

Mar 31, 2008

Hi all,

I have confusing problem here. My table which have about 150.000 rows have problem with updates on 15 rows.

It's access application which connect to sql server 2005 database. Access gets error:
"ODBC --update on linked table 'MyTable2 failed"

If i try to update on sql it's take couple minutes to update.
Evrything else on that table is ok.

View 11 Replies View Related

How To Do An Update On Existing Records?

Jul 20, 2005

I have one table of new records (tableA) that may already exist intableB. I want to insert these records into tableB with insert if theydon't already exist, or update any existing ones with new data if theydo already exist. A column (Action) in tableA already tells me whetherthis is an INSERT, UPDATE, or DELETE. I'm able to derive that I can doan insert withselect * into tableB from tableA where Action = 'INSERT'....and I think I can handle the delete.But I'm stuck on the update. How do I do the update? An ordinaryUPDATE statement just won't do unless I use a cursor to cycle throughthe recordset. I want to avoid a cursor.

View 1 Replies View Related

Use Db-lib To Update Database Records

Jul 20, 2005

How to use db-lib to update/insert database records without using SQLlanguage. I want to change the value of the data individually withoutplugging in the new values in the SQL language then execute it. Theperfect situation for me is loop through the retrieved records thenedit the values individually until EOF.Thanks,Carlo

View 1 Replies View Related

Update ID Field Of All Records

Aug 29, 2007

Hi everyone,

I'm fairly basic in SQL and I was wondering if it was possible to update the id field of a certain table in all records of that table. And to update the links to those ids in other tables.


Hope you understand and thanks in advance,
Tobias

View 12 Replies View Related

All The Records Update Or Just Half?

May 26, 2008

update top (50) percent table1 set fields1 = 1
update top (50) percent table1 set fields1 = 2

like the subject, all or half? thanks

View 6 Replies View Related

Update Table From Old Records

Apr 23, 2008



I'm using SQL Server 2000.

I have a table with data similar to this.... Multi field = 0 indicates current record, 1 indicates an old record

FWK NVQ Multi Key Start Date NVQ Date FWK Date
NULL NULL 0 123456 03/04/2006 NULL NULL
NULL NULL 1 123456 03/04/2005 01/09/2006 NULL
NULL NULL 0 234567 03/04/2006 NULL NULL
NULL NULL 1 234567 03/06/2005 04/10/2005 03/11/2005
NULL NULL 0 345678 03/04/2004 NULL NULL
NULL NULL 1 345678 03/07/2003 NULL 01/12/2003
NULL NULL 1 345678 03/08/2002 NULL NULL
NULL NULL 0 456789 30/09/2002 11/06/2003 NULL
NULL NULL 1 456789 29/08/2000 NULL NULL
NULL NULL 0 567890 30/09/2002 11/06/2003 11/06/2003
NULL NULL 1 567890 29/08/2000 30/05/2001 NULL
NULL NULL 0 678901 03/04/2006 01/09/2006 15/09/2006
NULL NULL 1 678901 30/03/2005 30/08/2005 15/08/2005
NULL NULL 0 789012 02/03/2000 03/09/2000 15/09/2000
NULL NULL 0 789013 30/06/2001 07/08/2001 14/08/2001


I need to update the table, setting the first two columns to the date of the old records... ie. I want the table to look like this...

FWK NVQ Multi Key Start Date NVQ Date FWK Date
NULL 01/09/2006 0 123456 03/04/2006 NULL NULL
NULL NULL 1 123456 03/04/2005 01/09/2006 NULL
03/11/2005 04/10/2005 0 234567 03/04/2006 NULL NULL
NULL NULL 1 234567 03/06/2005 04/10/2005 03/11/2005
01/12/2003 NULL 0 345678 03/04/2004 NULL NULL
NULL NULL 1 345678 03/07/2003 NULL 01/12/2003
NULL NULL 1 345678 03/08/2002 NULL NULL
NULL NULL 0 456789 30/09/2002 11/06/2003 NULL
NULL NULL 1 456789 29/08/2000 NULL NULL
NULL 30/05/2001 0 567890 30/09/2002 11/06/2003 11/06/2003
NULL NULL 1 567890 29/08/2000 30/05/2001 NULL
15/08/2005 30/08/2005 0 678901 03/04/2006 01/09/2006 15/09/2006
NULL NULL 1 678901 30/03/2005 30/08/2005 15/08/2005
NULL NULL 0 789012 02/03/2000 03/09/2000 15/09/2000
NULL NULL 0 789013 30/06/2001 07/08/2001 14/08/2001


Can anyone help me with this?
Jon




Code Snippet
DECLARE @TABLE_JR TABLE
([FWK] datetime,
[NVQ] datetime,
[Multi] Smallint,
[Key] varchar(10),
[Start Date] datetime,
[NVQ Date] datetime,
[FWK Date] datetime)
INSERT INTO @TABLE_JR VALUES (null,null,0,'123456','2006-04-03',null,null)
INSERT INTO @TABLE_JR VALUES (null,null,1,'123456','2005-04-03','2006-09-01',null)
INSERT INTO @TABLE_JR VALUES (null,null,0,'234567','2006-04-03',null,null)
INSERT INTO @TABLE_JR VALUES (null,null,1,'234567','2005-06-03','2005-10-04','2005-11-03')
INSERT INTO @TABLE_JR VALUES (null,null,0,'345678','2004-04-03',null,null )
INSERT INTO @TABLE_JR VALUES (null,null,1,'345678','2003-07-03',null,'2003-12-01' )
INSERT INTO @TABLE_JR VALUES (null,null,1,'345678','2002-08-03',null,null )
INSERT INTO @TABLE_JR VALUES (null,null,0,'456789','2002-09-30','2003-06-11',null)
INSERT INTO @TABLE_JR VALUES (null,null,1,'456789','2000-08-29',null,null)
INSERT INTO @TABLE_JR VALUES (null,null,0,'567890','2002-09-30','2003-06-11','2003-06-11')
INSERT INTO @TABLE_JR VALUES (null,null,1,'567890','2000-08-29','2001-05-30',null)
INSERT INTO @TABLE_JR VALUES (null,null,0,'678901','2006-04-03','2006-09-01','2006-09-15')
INSERT INTO @TABLE_JR VALUES (null,null,1,'678901','2005-03-30','2005-08-30','2005-08-15')
INSERT INTO @TABLE_JR VALUES (null,null,0,'789012','2000-03-02','2000-09-03','2000-09-15')
INSERT INTO @TABLE_JR VALUES (null,null,0,'789013','2001-06-30','2001-08-07','2001-08-14')

View 7 Replies View Related

Cannot Update Records That Contain NULL Values!!!

Oct 19, 2006

Hi,  I am trying to use a formView with an update button to update individual records in an sql database. (when i click update it doesnt perform the update and just refreshes the page. ) One of the fields in my records is a NULL - this is also one of the fields that i need to update. When i manually go into the database and enter some data, and then go back to my form, it updates fine, but as soon as i delete the data from the field, it returns to NULL and im back to square one. Any Ideas on how to get around this problem?THanks

View 4 Replies View Related

Update Command Is Updating All My Records!

Feb 16, 2007

I wrote a sproc which does four things:
1.  It looks at an option master to see if the record exists before inserting a new one
2.  If the record is not there it inserts the optino record
3.  Once the record is inserted I have to run a CASE statement on the record to determine its level
4.  Once the level is determined, the record needs to be updated with the correct level.
1-3 work fine (when run without the update command).
However, even though I set a criteria, UPDATE still updates and not the one records.
Any idea why? set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

--CREATE PROCEDURE [dbo].[sp_AddNewOption_OptionMaster_WithLevel]


@BuilderIDint,
@OptionIDINT,
@CommunityID INT,
@PhaseID INT,
@SeriesID INT,
@PlanID INT,
@ElevationID INT,
@CurrentSalesPrice Smallmoney,
@LocalComments Nvarchar (500),
@RoomID int,
@Package bit,
@Active bit

AS

--check to see if the option record exists

IF EXISTS (SELECT 1 FROM optionmaster WHERE BuilderID = @BuilderID AND OptionID = @OptionID AND CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID)

BEGIN
SELECT ' This option already exists in your Option Master'
END
ELSE BEGIN

--if the option record option does not exist, insert it

INSERT INTO [OptionMaster] ([BuilderID], [OptionID], [CommunityID], [PhaseID], [SeriesID], [PlanID], [ElevationID], [CurrentSalesPrice], [LocalComments], [RoomID], [Package], [Active], [DateAdded], [DateAvailable], [SalesPriceEffective], [OptionLevel])
VALUES (@BuilderID, @OptionID, @CommunityID, @PhaseID, @SeriesID, @PlanID, @ElevationID, @CurrentSalesPrice, @LocalComments, @RoomID, @Package, @active, GETDATE(), GETDATE(), GETDATE(),'10' )

SELECT ' Added to Option Master Successfully'
END

--once the option record is inserted, case it to find the its level (1-9)
--update the record with the approciate level.

UPDATE Optionmaster
SET optionlevel (

SELECT CASE WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '9' WHEN CommunityID = '0' AND PhaseID = '0' AND
PlanID > '0' AND ElevationID = '0' THEN '8' WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID > '0' AND
ElevationID > '0' THEN '7' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '6' WHEN CommunityID > '0' AND
PhaseID = '0' AND PlanID > '0' AND ElevationID = '0' THEN '5' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID > '0' AND
ElevationID > '0' THEN '4' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID = '0' AND ElevationID = '0' THEN '3' WHEN CommunityID > '0' AND
PhaseID > '0' AND PlanID > '0' AND ElevationID = '0' THEN '2' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID > '0' AND
ElevationID > '0' THEN '1'
END AS OptionLevel --provides the option level required to update the record
FROM optionmaster
WHERE (BuilderID= @BuilderID And OptionID = @OptionID and CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID))
--even through I specify the above criteria, it upates all records. 
 
 

View 9 Replies View Related

Easiest Way To Update Lots Of Records

Apr 26, 2007

I have a database where several thousand records have NULL in a binary field.  I want to change all the NULLs to false.  I have Visual Studio 5, and the database is a SQL Server 5 database on a remote server.  What is the easiest way to do this?  Is there a query I can run that will set all ReNew to false where ReNew is Null?  This is a live database so I want to get it right.  I can't afford to mess it up.Diane 

View 2 Replies View Related

Update Query Not Upadting Records?

Aug 19, 2007

dear experts,
The code written below doesnt report any error and doest serve the objective of updating the record in the database. Can you please suggest what can be the problem?
 Public Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim conLath As SqlConnection        Dim comLath As SqlCommand        Dim upcmd
        conLath = New SqlConnection("Data Source=heybabe1;Initial Catalog=sexsg;User ID=sa;Password=password")        conLath.Open()
        upcmd = "Update Hist set sett_price=@s_p, post_close=@p_s, vol_oq=@v_oq where hist_id=@hid"
        comLath = New SqlCommand(upcmd, conLath)
        comLath.Parameters.Add("@s_p", Data.SqlDbType.Decimal, 8)        comLath.Parameters.Add("@p_s", Data.SqlDbType.Decimal, 8)        comLath.Parameters.Add("@v_oq", Data.SqlDbType.Int, 8)        comLath.Parameters.Add("@hid", Data.SqlDbType.Int, 8)
        comLath.Parameters("@s_p").Value = IIf(sett_price.Text = "", DBNull.Value, sett_price.Text)        comLath.Parameters("@p_s").Value = IIf(post_close.Text = "", DBNull.Value, post_close.Text)        comLath.Parameters("@v_oq").Value = IIf(vol_oq.Text = "", DBNull.Value, vol_oq.Text)        comLath.Parameters("@hid").Value = Request.QueryString("hid")
        Try
            comLath.ExecuteNonQuery()            Response.Redirect("admindata.aspx")
        Catch ex As SqlException            If ex.Number = 2627 Then                Message.InnerHtml = "ERROR: A record already exists with " _                   & "the same primary key"            Else                '     Message.InnerHtml = "ERROR: Could not update record, please ensure the fields are correctly filled out"                Message.InnerHtml = ex.Number                Message.Style("color") = "red"            End If        End Try        comLath.Dispose()        conLath.Close()
    End Sub

View 5 Replies View Related

DataAdapter.Update Updates Zero Records

Jul 13, 2004

I am having trouble getting an Update call to actually update records. The select statement is a stored procedure which is uses inner joins to link to property tables. The update, insert, and delete commands were generated by Visual Studio and only affect the primary table. So, to provide a simple example, I have a customer table with UID, Name, and LanguageID and a seperate table with LanguageID and LanguageDescription. The stored procedure can be used to populate a datagrid with all results (this works). The stored procedure also populates an edit page with one UID (this works). After the edit is completed, I attempt to update the dataset, which only has one row at this time, which shows that it has been modified. The Update modifies 0 rows and raises no exceptions. Is this because the update, insert, and delete statements do not match up one-to-one with the dataset? If so, what are my choices?

View 1 Replies View Related

Update Records Just Selected (in One Query)

Mar 28, 2006

I have a stored procedure that I give some parameters and it joins a couple of tables and returns the results.  I would like to know how to update the records in one of the original tables before returning results.
CREATE Procedure dbo.DS_GetSomething(@someValue char(5)AS
Select table1.* ,table2.* FROM table1 left inner join table1.itemid on table2.itemid where table2.someValue = @someValue
The table has a field of IMPRESSIONS that I would like to do something like:
;UPDATE table1 set impressions=impressions + 1 where recordid=??
I don't know how to access the recordids I just got from the original select statement.
Thanks for any help
Greg

View 7 Replies View Related

Help! A Simple &#39;update Records&#39; Question

Nov 19, 1999

Hi there,

I was trying to update records of a recordset(ADODB.Recordset) returned from a stored procedure(SQL server 7.0) in ASP file, this stored procedure select records into a temporary table, so the records returned by the SP actually physically are in tempdb database rather than in the user database.

When executing Update statement , I got the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Database name 'Mydatabase' ignored, referencing object in tempdb.

Any ideas/comments would be highly appreciated!
Dana Jian
dan_jian@hotmail.com

View 1 Replies View Related

Update Records With First Found Data

Dec 20, 2004

I am trying to combine 2 tables, one is an event table, the other is a system table.

I would like to update field system.transaction with the first event.transaction

Where event.account = system.account
and event.effdt > system.effdt
and event.effdt <= system.effdt + 30

It's not really a join so I'm not sure how to write it.

Thanks,
Doug

View 2 Replies View Related

How To Update Records With Data From Another Table

Sep 20, 2011

I want to update table2.message based on the criteria of table1.name. for example, all records named John will be updated with 'Msg1' in table 2.message. I am using MS SQL 2000 and below is the scenario.

table1 columns
ID
Name

table2 columns
ID
Message

Select a.Id, a.name, b.message
from table1 a, table2 b
where a.id =b.id

a.id a.name b.message
1 John Msg1
2 Steve Msg2
3 Scott Msg3
4 John NULL - update b.message to 'Msg1'
5 Steve NULL - update b.message to 'Msg2'
6 Scott NULL - update b.message to 'Msg3'
7 John NULL - update b.message to 'Msg1'
8 Steve NULL - update b.message to 'Msg2'

If i will update the record per name i am using the query below and i am pre-selecting all the existing names.

update table2 b
set b.message=(Select top 1 b.message
from table1 a, table2 b
where a.id =b.id

[Code] ...

How to update this in bulk without preselecting all the names?

View 7 Replies View Related

Update Statement Not Updating All Records

Jul 27, 2012

I have the following code:

TRUNCATE TABLE [Temp_Export];
INSERT INTO [Temp_Export]
(

[Code]....

The issue I'm having is that I am getting more records in the VIEW than records updated. What can explain such a discrepancy? I am updating the records based on the PK/FK Temp_Import_ID column, which exists in both tables. where the view would yield more records than those matched by the update statement?

View 6 Replies View Related

Update One Record And OUTPUT Two Records

Feb 1, 2013

For a staging table

Table TimeRange
id binary(16),
startTime datetime,
endTime datetime,
isValid tinyint

There are two validation rules: starttime cannot be null, endTime cannot be null (assume that we cannot set the columns as NOT NULL).

We would like to OUTPUT an error record for each validation error for every record in the TimeRange table.

Would there be a single statement that could do this ? (ie. would UPDATE invalid record AND would OUTPUT two validation error records for a record that has startTime = NULL AND endTime = NULL)

Something like:

UPDATE TimeRange
SET isValid = 0
OUTPUT inserted.id,
CASE WHEN inserted.startTime is NULL THEN inserted.startTime
WHEN inserted.endTime is NULL THEN inserted.endTime
END -- Needs to handle the case where both startTime and endTime are invalid
INTO @InvalidRecords
FROM (a SELECT stmt that is a table with a record for each validation error)

MERGE does not have the functionality needed (inserting multiple records for every invalid record).

Have not had success using a UNION ALL, as there is an error updating derived tables.

Have not had success creating a JOIN statement.

View 7 Replies View Related

Update Records Base On Dates?

May 12, 2014

I have a table like this one, with PurchaseID be the primary key.

Code:

PurchaseIDItemIDQtyDatePurchased
1105152010-01-12
210742012-11-30

[Code]...

But I can't figure out how to get the max DatePurchased of the same ItemID.

View 4 Replies View Related

Update Group By Changed Records?

Nov 5, 2014

I'm bulk loading employees into an etl table, each employee has a unique ID number, but they have multiple records in the data. Sometimes their name or birthdate will change and I want to identify those records and only insert the newest version into production. I can do this with a series of temp tables, but I'm sure there's a better way. The SQL below updates the etl table with the flag I want to mark the inserts, but it seems convoluted. (Jon's birthday changes, Jane's birthday changes, Bill's gender changes, Amy nothing changes(I handle those inserts later))

DECLARE @Records TABLE(
firstname varchar(50), lastname varchar(50), birthdate date, sex char(1), IDNum varchar(15), moddate date, opflag char(1))
INSERT INTO @Records
VALUES
('JON','SMITH','20000101','M','12345','20140101','I'),
('JON','SMITH','20000101','M','12345','20140201','I'),

[code]....

View 1 Replies View Related

Function To UPDATE Multiple Records

Jan 7, 2015

I have a question, what kind of Query or function do I have to use to UPDATE multiple record that I just want to add a letter at the end of the existing OrderNum.

Before
ID NAME OrderNum
1 Pete WEB123
2 Paul WEB124
3 Sam WEB125
4 Tim WEB126

After
ID NAME OrderNum
1 Pete WEB123A
2 Paul WEB124A
3 Sam WEB125A
4 Tim WEB126A

View 3 Replies View Related

UPDATE RECORDS WITH AGGREGATE FUNCTION

Mar 2, 2007

Problem is very simple

I want to update sum of a field from another table to first table

TABLE ONE:
==========
ItemID
QtyInStock


Table TWO:
==========
BatchID
ItemID
Qty

I want to Update the QtyInStock of First Table with Sum(Batch.Qty)

here is the query i am writing but giving error

UPDATE ITEMS
SET
INSTOCKQTY=CASE WHEN QtyInBatch>1 THEN QTYINBATCH ELSE 0 END
FROM ITEMS, (
SELECT ITEMS.ITEMID, SUM(Batch.Qty) AS QtyInBatch FROM Batch INNER JOIN Items ON Batch.ItemID = Items.ItemID GROUP BY ITEMS.ITEMID
)

appericiating anyones help in advance




FAZEEL AMJAD
Systems Engineer
Crystal Technologies

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved