Update Spends 1800 Times More Than Select
Nov 7, 2005
Hello,
The select statement needs only 1 second to complete the query.
But the update statement spends 30 minutes. Why?
SELECT STATEMENT:
declare @IDate smalldatetime
select @IDate=col001 from USDay
select * from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US990010 and A.col002=B.US990020
where B.US990010 is not null
UPDATE STATEMENT:
update US99000D
set US990030=A.col003,
US990040=A.col004,
US990050=A.col005,
US990060=A.col006,
US990070=A.col007
from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US990010 and A.col002=B.US990020
where B.US990010 is not null
INDEX:
clustered index: US990020, US990010
non-unique index: US990010, US990020
View 4 Replies
ADVERTISEMENT
Mar 15, 2006
hi Dear All
How can i import data in SQL Server 2000 which contains the value of date field from 1800 yrs to till now
eg. 15/12/1860, 10/10/1896, 10/10/2006
but if i change the year of date value to 19?? then it's imported.
anybody can help me please!
Thanks in advance
Rahman
View 3 Replies
View Related
Nov 9, 2015
I am trying to update the same row of the table multiple times. in other words, i am trying to replace the two stings on same row with two different values.
Example: if the column has a string "b" then replace with "B" and  if the column has a string "d" then replace with "D" . I can  write multiple updates to update it but i was just wondering if it can be done with single UPDATE statement
column before the update : bcdxyz
after the update: BcDxyz
Following is the sample data
declare @t1 table
(
  c1 varchar(5),
  c2 varchar(10),
  c3 varchar(5)
)
insert into @t1 values ('a','bcdxyz','efg')
[Code] ....
View 5 Replies
View Related
Aug 5, 2004
Hi All...
if i had the following sp...
*******************************************************
create procedure my_insert (param1 int, param2 int, paramx int)
as
...
complicated insert routine
...
return
*******************************************************
and then i wanted to exec this sp in another procedure i would have
exec my_insert( 1_value, 2_value, 3_value )
My question is how could i exec this will the result set of a select.... something like this
exec my_insert (select 1_value, 2_value, 3_value from another_table).
I know i could have this in an insert result type statement ie...
insert into dest_table (select 1_value, 2_value, 3_value from another_table)
but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement
Many Thanks
Gary T
View 4 Replies
View Related
Sep 14, 2006
The developers of frond end ask to select only one row from one table, but many times, such as 10. That means the output has 10 rows and same.
Any suggestion will be appreciated.
ZYT
View 4 Replies
View Related
Jul 20, 2005
This seems so simple yet I can't figure it out.I have a table that has two important columns for this query.Column A has varcharColumn B is datetimeAll I want to do is select any record that is between 5:00 am and10:00 am regardless of what date it falls under.In my brain I seeSelect *From <table>Where B Between Like ‘%5%am' and Like ‘%10%am'This chokes and I suppose its because Between is literal and Like isvariable.I cant convert column B to just times because I still need to see thedate in the results of the query.Thanks in advance for help
View 2 Replies
View Related
Jun 11, 2014
I have this update statement that I need to have joined by MSA and spec.
I keep getting an error.
Msg 1011, Level 16, State 1, Line 3
The correlation name 't1' is specified multiple times in a FROM clause.
Here is my statement below. How can I change this?
UPDATE MSA
SET [Count on Billed Charges] = (Select Count(distinct[PCS Number])
From MonthEnds.dbo.vw_All_Products t1
Inner Join MonthEnds.dbo.vw_All_Products t1 on t1.[MSA Group] = t2.[MSA Group] and t1.[Spec 1] = t2.[Spec 1])
View 3 Replies
View Related
Jul 23, 2005
I have a single update statement that updates the same column multipletimes in the same update statement. Basically i have a column thatlooks like .1.2.3.4. which are id references that need to be updatedwhen a group of items is copied. I can successfully do this withcursors, but am experimenting with a way to do it with a single updatestatement.I have verified that each row being returned to the Update statement(in an Update..From) is correct, but that after the first update to acolumn, the next row that does an update to that same row/column combois not using the updated data from the first update to that column.Does anybody know of a switch or setting that can make this work, or doI need to stick with the cursors?Schema detail:if exists( select * from sysobjects where id = object_id('dbo.ScheduleTask') and type = 'U')drop table dbo.ScheduleTaskgocreate table dbo.ScheduleTask (Id int not null identity(1,1),IdHierarchy varchar(200) not null,CopyTaskId int null,constraint PK_ScheduleTask primary key nonclustered (Id))goUpdate query:Update ScheduleTask SetScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.')FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0Query used to verify that data going into update is correct:selectScheduleTask.Id, TaskCopyData.Id, ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.'FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0
View 8 Replies
View Related
May 28, 2015
I have a column with XML data stored in it. I need to update that column several times with new values for different nodes. I've written a CLR function to update the XML quickly but the update is always based on the initial value of the xmlData column. I was hoping that the subsequent updates would be based on the new data from the prior update (each xmlTable has several newData rows). Do I have to make this a table valued function and use cross apply?
UPDATE xmlTable
SET xmlTable.xmlData = Underwriting.UpdateByDynamicValue(xmlTable.xmlData,newData.NodeID,newData.NewValue)
FROM xmlTable
JOIN newData
ON xmlTable.ID = newData.fkXmlTableID
View 2 Replies
View Related
Jun 20, 2013
Problem Summary: Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?
I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look very similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.
Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.
Server Configuration:
SQL Server 2008 R2 SP1, Enterprise Edition
3 x Quad-Core Xeon Processor
Max Degree of Parallelism = 8
148 GB RAM
SQL Code:
Target Table:
USE TPS;
IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL
DROP TABLE dbo.ParticipantResponse;
[code]....
View 9 Replies
View Related
Apr 24, 2007
Hello everyone,
I have the following sql query which basically gives me 10 ramdon records in a table, what i want to do is update those records selected. i.e update the select query.I want to upddate a field in the 10 records returned by the query below. Thank you.
SELECT TOP (10) *FROM tblSiebelActivity1000WHERE (DATEPART(mm, dteActivityDueRT) = MONTH(GETDATE())) AND (DATEPART(dd, dteActivityDueRT) = DAY(GETDATE())) AND (txtServiceRegion = 'nj02')ORDER BY NEWID(), txtActivityNumber
View 6 Replies
View Related
Jul 3, 2006
I have two tables. I need to put the first character in column2 table 2 into column2 table 1 where there id's are the same. The table where the information is coming from(table2) is an int datatype and the table where it is going to(table1) is a char datatype. Any help is appreciated, thanks in advance.
View 1 Replies
View Related
Mar 4, 2005
I have a sales table that has a customer_number, salesman_number and sales data (like item, quantity, etc), there is a customer table that has the customer_number, name, address, etc. and salesman_number, with the passign of time salesmen come and go and the customer service department is in charge of modifying the customer table and assign the new salesman number to them. All the previous sales have to be reasigned to this new salesman even though it wasnt him the one that sold the items (I know, I know, thats the way the company requieres it, this is an old system). Now to my question:
I have this select query that gives me the rows from table sales that have to be modified:
SELECT C.sales_rep,S.salesrep,S.*
FROMSALES AS S
INNER JOIN customer AS C
ONC.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = S.site_code AND
C.cust_no = S.cust_noAND
C.cust_sffx = S.cust_sffxAND
C.division = S.divisionAND
C.sales_rep != S.salesrep
WHERES.month = 2AND
S.year = 2005
ORDER BYC.sales_rep
I just want to know if my approach to convert this query from an update to a select is ok:
UPDATE SALES AS S
SET S.salesrep = C.sales_rep
INNER JOIN customer AS C
ONC.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = S.site_code AND
C.cust_no = S.cust_noAND
C.cust_sffx = S.cust_sffxAND
C.division = S.divisionAND
C.sales_rep != S.salesrep
WHERES.month = 1AND
S.year = 2004
Thanks for your help
Luis Torres
View 6 Replies
View Related
Jan 25, 2007
Hi there.
I have two databases, one called PWALive and one called Portal-Live.
what I want to do is create an extract from Portal-Live of e-mail addresses and then use this date to update a table in PWALive.
Here are the relevant tables
Portal-Live
tblMUD(MUD_ID, MUD_clock, MUD_email ... )
PWALive
employee(employee_number, email_address ... )
Relationship: MUD_clock = employee_number
My sql statement for the extract from Portal-Live.tblMUD looks like this:
SELECTMUD_clock,
MUD_email
FROM tblMUD
WHEREMUD_email IS NOT NULL
AND MUD_clock IS NOT NULL
I want to then update the correct employee in PWALive.employee:
UPDATE employee
SET ... --Not entirely sure what to do here!
WHERE employee_number = MUD_clock
How can I do this in one sql sequence/is it possible?
As I understand it I can use CONNECT TO but Icant get that to work, let alone know how to link the update with the first sql statement.
Big thank you in advance
-GeorgeV
View 14 Replies
View Related
Oct 25, 2007
Hi
how can you update many fields from a select ? :
UPDATE table1 (a1,b1,c1) SELECT (a2,b2,c2) FROM table2 WHERE table1.id = table2.id
for MS SQL 2000/25000
thank you for helping
View 3 Replies
View Related
May 19, 2008
I have a select statement
SELECT ORDHFILE.OABL, ORDTFILE.ODLOTSEQ, ORDHFILE.OACUSTPO, ORDHFILE.OABLDATE, ORDHFILE.OAREGN, ORDHFILE.OASHIPDTMM, ORDHFILE.OASHIPDTDD, ORDHFILE.OASHIPDTYY, ORDHFILE.OASHIPVIA, ORDHFILE.OAFOB, ORDHFILE.OAATTN, CUSTFIL2.COBILLNUM, CUSTFIL2.COCNTRY, ORDHFILE.OASFNAME, ORDHFILE.OASFAD1, ORDHFILE.OASFAD2, ORDHFILE.OASFCITY, ORDHFILE.OASFST, ORDHFILE.OASFZIP, ORDHFILE.OASFZIP2, ORDHFILE.OASTNAME, ORDHFILE.OASTAD1, ORDHFILE.OASTAD2, ORDHFILE.OASTCITY, ORDHFILE.OASTST, ORDHFILE.OASTZIP, ORDHFILE.OADESC1, ORDHFILE.OAOPID, ORDTFILE.ODPNUM, PRODFIL2.PUPNAME1, PRODFIL2.PUPNAME2, ORDTFILE.ODQORD, ORDTFILE.ODMEAS, ORDTFILE.ODUM, ORDTFILE.ODCSC, ORDREM.OCCPREM1, ORDREM.OCCPREM2, ORDREM.OCCPREM3, ORDREM.OCCPREM4, ORDREM.OCCPREM5, ORDREM.OCCPREM6, ORDREM.OCCPREM7, ORDREM.OCCPREM8, ORDREM.OCCPREM9, ORDREM.OCCPREM10, ORDREM.OCCPREM11, ORDREM.OCCPREM12
FROM B108FFCC.CHEMPAXSNF.CUSTFIL2 CUSTFIL2, B108FFCC.CHEMPAXSNF.ORDHFILE ORDHFILE, B108FFCC.CHEMPAXSNF.ORDTFILE ORDTFILE, B108FFCC.CHEMPAXSNF.PRODFIL2 PRODFIL2, B108FFCC.CHEMPAXSNF.ORDREM ORDREM
WHERE (((ORDHFILE.OAWHS)='19') AND ((CUSTFIL2.COCNUM)=(ORDHFILE.OACNUM)) AND ((ORDTFILE.ODBL)=(ORDHFILE.OABL)) AND ((PRODFIL2.PUPNUM)=(ORDTFILE.ODPNUM)) AND ((ORDREM.OCBL)=(ORDHFILE.OABL)) AND ((ORDREM.OCPNUM)=(ORDTFILE.ODPNUM)) AND ((ORDREM.OCMEAS)=(ORDTFILE.ODMEAS)))
ORDER BY ORDHFILE.OABL, ORDTFILE.ODLOTSEQ
It works fine. However the data is being duplicated every 15 minutes instead of updating the changes. I am getting the same information over and over again.
I'm using DTS SQL Server Enterprise Manager.
View 6 Replies
View Related
Jun 29, 2006
Is it possible to update an entire column with data from another table? I have allot of information that is date sensative and would rather just update one column rather than havnig to change the date all the time. I have started this string and am stuck about what tp put after the SET string
UPDATE scf_yfeesycom
SET netrealpl =
SELECT netrealpl
FROM scf_pandl
Where date > 2006-01-31
View 2 Replies
View Related
Mar 25, 2008
I have a huge table with 407 columns and around 90 million records.
I need to run an update on 10 columns..
updates as case, trimming, replace.....
What do you guys suggest? Should I run select into or run an update query that runs all night.?
Thanks!
View 5 Replies
View Related
Sep 21, 2006
I need to update a table as follows:
Update item1, item2, item3, item4 in a table where column1="email_address" and column2 is ordered acending so the most recent entry for a specific user is updated.
column2 is a time stamp.
Pseudo code something like this:
UPDATE Table1 SET item1, item2, item3, item4 WHERE Table1 is TOP 1 ORDERED BY column2 ASC column1="email_address"
So i want to update only the most recent entry of a customer with a specific email address. I can't get the SQL command together that will do that.
I aint that bright so specific syntax always helps.
Thanks,
Bill
View 1 Replies
View Related
Feb 28, 2008
Hi there. My question is: for example i have a table Test:Id, pId, Name, Count. Is that possible to execute select query and update query in one query? It means to select all records where Name = 'somename' and update Count field of all selected records? Thanks!
View 10 Replies
View Related
Apr 4, 2008
Hi,
I have an insert statement like:
INSERT INTO
table2(
field1
)
SELECT
field1
FROM
table1
What I would like to be able to do is then update a field in table1 with the new identity created by the insert in table2...is there a way I can do this with SQL?
More Info:
The select actually groups the information as to merge data together into table2.
Any ideas would be great!
Thanks,
CES
View 12 Replies
View Related
May 30, 2007
I need to Update a table with information from another table. Below is my psuedo code - need help with the syntax needed for Sql2000 server.
JOIN tblStateLoc ON tblCompanies.LocationID = tblStateLoc.LocationIDUPDATE tblCompaniesSET tblCompanies.StoreType = tblStateLoc.StoreTypeWHERE tblCompanies.LocationID = tblStateLoc.LocationID
View 2 Replies
View Related
Mar 10, 2008
Is there some sort of sql command where a tuple in a table has one of its cells updated depending on the value of a cell from another table. Please I would appreciate some help.
Thanks
View 1 Replies
View Related
Sep 18, 2001
In answering this question you could just tell me what you think would work rather than trying to test it.
The following query works correctly and returns rows with pairs of values repid and repid2. I need loop through a large table and update *its* repid2 with rm_repid2 for any rows where its repid value equals rm_repid but only for the pairs that result in the below query
SELECT
rm.repid AS rm_repid
,rm.repid2 AS rm_repid2
,rm.id2contact
,r.repid AS r_repid
,r.prim AS r_prim
FROM sfrep r JOIN sfrepmst rm ON r.repid=rm.repid2
WHERE rm.repid IN
(
'TEST01'
,'TEST02'
)
Returns
rm_repid rm_repid2 id2contact r_repid
-------- --------- ---------------------------------------- -------
TEST01 NEW01 John Smith NEW01
TEST02 NEW02 Ken Roberts NEW02
TIA
Doug
View 1 Replies
View Related
Nov 6, 2013
i want to select a data from a employee table and update that select data to attendlog1 table with match Eid
this is a employee data
EID----------bid
26478---------2
this is a attendlog data
EID-------------EBID
26478------------NULL
i want this type of result
EID-------------EBID
26478------------2
BID and EBID fields are change but values are same.
View 2 Replies
View Related
May 13, 2007
Hi all, have a pretty simple query. but am new to workin in multiple tables. This statement shows
Items in table1 that exists in table2 that match a filter.
I would like to change this query to update the TOBEDELETED column to 'F' based on the same column and filter match.
SELECT ITEMS.ITEMNO, ITEMS.TOBEDELETED
FROM ITEMS
INNER JOIN QLOG
ON ITEMS.ITEMNO=QLOG.SOURCEID
WHERE QLOG.SOURCE= 'ITEMS'
Thanks all in advance
View 4 Replies
View Related
Oct 26, 2007
i am selecting the top 25 records from a table for processing. i would like to set the processstatus field to 3 as soon as i select the records.
is there a way to do this in one query?
View 17 Replies
View Related
Nov 20, 2007
Hi Friends,
I need a select query to identify how many records violate the basic dateformat, later on i update the junkdata with null.
Initially I have a database, in which one field(for Date) is given as Varchar, now as we know varchar accepts all types of data, when migrating the same data to another server, i am using Date as datatype for the new field and want to remove all other format's of data (Junk data) entered to that field and want to ratain only the general format i.e, MM/DD/YYYY or MM/DD/YYYY, this query should also support MM<=12, DD<=31
Regards,
Prasad K
View 2 Replies
View Related
Feb 8, 2008
How to Use select statement when using Update statement.
Eg: Update program_video set program_id=(select id from program where recordname='122'), stopnumber=stopnumber*-1 where stopnumber < 0
Thanks & Regards
Jagadeesh
View 7 Replies
View Related
Jul 20, 2005
I have a table with only one row to get unique ID-numbers. To get the nextID-number I have to increase the number in the row: first, get the numberfrom the database, and then do the update.Some ASP-code:Set rs = db.execute("SELECT id FROM tbl_id")id = rs("id")db.execute("UPDATE tbl_id SET id ="& id + 1 &"")This is not safe: two different sessions cat get the same ID.Is it possible to increase the value of id in tbl_id and get the value atthe same time? Or is there any other way?/matte
View 6 Replies
View Related
May 5, 2008
HI Guys,
I have two table t1 and t2
t1 has a,b,c columns(no primary key defined)
t2 has a,b columns (a is primary key)
I want to update the table t2 with follwin query
update t2 set b=(select avg(b) from t1 group by a) where a=(select a from t1 group by a)
Is it possible to update table of result of other query?
or give me other solution for that how can i do that thing...?
View 1 Replies
View Related
Apr 5, 2007
In Dot net 2.0 we change using SQLDataSource to Conect with SQLDB.
Now for My case ,the Select SQL is dynamic when differnece user and parameters to the page, So if I want to Update the data input by user,then I must give Update/insert/delelte SQL to SQLDatasource's InsertCommand /UpdateCommand/DeleteCommand .
How to Generate the Insert/update/delete command for the SQLDataSource ? as in dot net 1.1 can use SQLCommandBuilder to generate it,but SQLCommandBuilder just support DataAdeptor not for SQLDataSource, Could any body know how to do it when the SelectCommand is dynamic and need to update data back to DB after edit?
thanks a lot.
View 4 Replies
View Related
May 7, 2007
I have a table which I need to obtain data from but am having a problem with select statement.
Specifically, I have a table that has multiple records for a particular hostName where the name of the host is either a shortname (say "Larry") or a long name (say "Larry's").
I need to display only the long names (NOT THE SHORT NAME records with the similar hostName).
Select winsHostName, len(winsHostName) from winsData where winsClientIPAddress IN (SELECT winsClientIPAddress from winsData Group By winsClientIPAddress Having count(winsClientIpAddress) > 1) Order by winsHostName
Which returns data
Name Length
ATVDDR 6 ATVDDR1 7
This is a s far as I can get but,
Now, I need to list all remaining table fields based on the Name with the longer length and this is where I run into trouble.
The output should read per below with the remaining table fields which I cannot seem to extract with nested select statement
Name IP Addr Location
ATVDDR1 1.1.1.1 2ndFloor
I tried adding another GROUP BY by this gets me no where because I do not need to execute another aggregate in select statement. Maybe I need to use INNER JOIN
Please advise any assistance.
View 2 Replies
View Related