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

Err While Importing Data Containing Value Of Date Field Is Of Yrs.1800

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

Transact SQL :: Update Same Row Multiple Times

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

Exec SP Many Times, From Select?

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

How To Select One Record Many Times?

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

Select Records Between Two Times

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

Update Statement - Correlation Name Is Specified Multiple Times In FROM Clause

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

Updating The Same Column Multiple Times In One Update Statement

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

Transact SQL :: Need To Update That Column Several Times With New Values For Different Nodes

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

SQL Server 2008 :: Merge Statement Takes Several Times Longer To Execute Than Equivalent Update

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

Update Select?

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

Update Select

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

From Select To Update

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

Select From One DB - Update In Another

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

Update From Select

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

Select And Then Update

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

Select/Update

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

Select Into Or Update

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

Need Update Of Select Using TOP 1

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

Select/update

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

SELECT INTO And The UPDATE...

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

SELECT, JOIN And UPDATE

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

Update And Select From Two Tables.

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

Need To Convert This Select Into An Update

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

Select And Update Query?

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

Got The Data With Select, How To Update?

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

Select And Update In Same Query

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

Select Query And Update

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

How To Use Select Statement During Update

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

SELECT And UPDATE In The Same Query

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

Update Query With Select

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

How To Update When Dynamic Select SQL By Using SQLDatasouce In .net 2.0?

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

Select And UPDATE Statement Help! (Using SQL Server)

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







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