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.
Hi everyone.... I'm trying to execute this update statement... It takes an eternity... any ideas on how to rewrite or speed it up?
It's a several step process... below is everything that i run, one step at a time. The final update statement is what takes so long. It should only affect about 2600 rows out of a potential 9000. That's why I'm confused on the response time
select d.olddevicename, de.device, d.newdevicename into #temp9
from dns d, devices de
where de.device = d.olddevicename
update #temp9 set device = newdevicename where olddevicename = device
update devices set device = #temp9.device from #temp9, devices where #temp9.device in
(select #temp9.device from #temp9, devices where #temp9.olddevicename = devices.device)
Thank you!
J.
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 RelatedHi,
I have a table with 48 million rows,when i executed following update query it is taking 10 HOURS in SQL SERVER 2000 with SP1.
Where as when i executed same query in SQL SERVER7.0 with same table then it is taking 13 MINUTES. Comming to Machine...SQL 2000 Server has more processors and greater memory than SQL 7.0 m/c.
It looks strange but this is true.Does any one faced such problem..is there any bug in SQL 2000?????
Here is Query::
update cus_pay_jan_dist set univ_regdate = b.dayid
from cus_pay_jan_dist a with (nolock), tm_dayids b with (nolock)
where a.univ_regdate = b.dayidnum and a.univ_regdate like '2001%'
Thanks
Ananth
Hi There,I have an update statement to update a field of a table (~15,000,000records). It took me around 3 hours to finish 2 weeks ago. After thatno one touched the server and no configuration changed. Untilyesterday, I re-ran it again and it took me more than 18hrs and stillnot yet finished!!!What's wrong with it? I can ran it successfully before. I have triedtwo times but the result was still the same.My SQL statement is:update [all_sales] aset a.accounting_month = b.accounting_monthfrom date_map bwhere a.sales_date >= b.start_date and a.sales_date < b.end_date;An index on [all_sales].sales_date is built successfully.A composite index on ([date_map].start_date, [date_map].end_date) isbuilt successfully.My server config is:SQL Server 2000 with Service Pack 3Windows 2000 with Service Pack 4DELL PowerEdge 6650 ServerDUAL XEON 1900MHz Processors2G RAM2G Page File on Drive C2G Page File on Drive DDELL Diagnostics on all SCSI harddisks were all PASSED.Any experts could simly give me a help????Thanks x 1,000,000,000
View 4 Replies View RelatedWe have a simple UPDATE query, joining two tables, that takes much longer than 10 hours to run, but if we break the table in six (10 million rows in each table), it takes only fifteen minutes to run each part.
Why? And how can we tell in advance whether a query will cross the threshold into l.o.n.g.r.u.n.n.i.n.g query? Or, how can we prevent it?
The system is Windows XP Pro with 4GB RAM (/3GB switch), and SQL Server Standard 2005. Log files, swap files, dbf files are on separate drives. The system is dedicated to SQL Server. No other queries are running at the same time. The database is in Simple logging mode. Each table is a few GB with 60 million rows.
An example problem query is: (updating fewer than 10 bytes)
UPDATE bigtable
SET bigtable.custage = scores.custage, bigtable.custscore = scores.custscore
FROM bigtable
JOIN t2 ON bigtable.custid = scores.custid
In this case, each table has 60 million rows. 'custid' is a sequential, unique integer. SCORES table is clustered on 'custid' and is 1.5GB in size. BIGTABLE has an index on 'custid', and is 6GB in size. There is a one-to-one match between the tables on 'custid', but not enforced. The SCORES table was created by exporting a few fields (but all 60 million records) from BIGTABLE, updating the values in a separate program, then importing back in SQL Server into the SCORES table.
The first time this query was run, we stopped it after it ran 16 hours. When we broke up the bigtable into 10 million record chunks (big1, big2, big3..., big6) each update only took 15 minutes, for 90 minutes total.
* How can in we tell in advance that the full chunk would take more than a few hours?
* Why is it taking SO MUCH LONGER than in smaller chunks?
* When a query is taking that long to run, is there any way to tell where in the plan it is?
* What should we do differently?
Thanks for any help; this is a real head scratcher for us.
I have an update statment in my SSIS that use to take 10 minutes in SQL 2000 dts and now its take 1 hour 15 minutes in SQL 2005.
this is my sql update statment -
Update WeeklySalesHistory set
weekendingdate =
(SELECT LastTransDateTime from ReplicationControl
where TableName = 'WEEKHST')
where weekendingdate is null
It is using ole db connection. About 36,000 records that it is updating.
I have read ole db can be slow and to use staging table. Does that mean on all updates like this I have to use a staging table and then insert. I didn't use to have to do this in SQL 2000. Has it changed. Are there any other options?
any input greatly appreciated.
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
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
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
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
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
I have this code
I want to update nilai = total where kd_bulan = 9 and nilai = 0 where kd_bulan = 12
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 ;
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
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 RelatedHow 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 RelatedHi 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
update top (50) percent table1 set fields1 = 1
update top (50) percent table1 set fields1 = 2
like the subject, all or half? thanks
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')
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 RelatedI 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.
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 Relateddear 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
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 RelatedI 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
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
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
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?
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?
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.
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.
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]....