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


ADVERTISEMENT

Transact SQL :: Running Query For Multiple Times Through The Day?

Jun 12, 2015

I want  the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .

there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in job 
 
INSERT INTO [dbo].[Audit_Active]
([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName])
 SELECT   
--DISTINCT  
p.SPID,
p.LogiName,
p.HostName,   

[Code] ....

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

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 :: Avoid Same Table Multiple Times Rather Than Put Records In Single Table And Use It Throughout

Nov 19, 2015

There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule

SELECT  
 PE.PropertyExternalReferenceValue  [BAReferenceNumber]
, PA.DescriptionCode
    [PSDCode]
, PV.ValuationEffectiveDate
    [EffectiveDate]
, PV.PropertyListAlterationDate
    [ListAlterationDate]

[code]....

Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set  tables multiple times 

View 11 Replies View Related

Transact SQL :: How To Update Multiple Records In A Table

Jul 23, 2015

I need to update more than one record at once. I have ~ 100 records that I have to update and don't want to execute query 100 times.

My query looks like this:

Update Table1
Set Table1.field1 = ( select Table2.field1 from Table2 where Table2.field2 IN ('a', 'b', 'c')
where Table1.field2 IN ( 'a', 'b', 'c')

It obviously failed because subquery returned more than one value and error message stated that I can't use '=' operator in this case.

My question: how could I update the same column from many records in one execution?

View 10 Replies View Related

Transact SQL :: How To Update ItemOrder For Multiple ItemID

Aug 25, 2015

I have a table with some below dummy data,

DECLARE @TBL TABLE (QId INT, ItemName NVARCHAR(5), ItemParentName NVARCHAR(5) NULL, ItemId INT, ItemOrder DECIMAL(18,2) NULL)
INSERT INTO @TBL VALUES
(1, 'A', NULL, 10, 1.00),
(2, 'A1', 'A', 10, NULL),
(3, 'A1', 'A', 10, NULL),

[Code] ....

1. I have multiple "ItemId" and have their parent/child data.
2. Need to update only child's "ItemOrder". Example, if parent has order "1.00" then their child will be 1.01 & 1.02... and so on.

View 6 Replies View Related

Transact SQL :: Conditional Update A Field From Multiple Tables

Sep 9, 2015

Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.

View 13 Replies View Related

Transact SQL :: Update Statement To Include Multiple Records At Once

Apr 20, 2015

I have this update statement that works for one record. How do I write it to include multiple records at once. Please see sample below.

update
mklopt
set
 FRMDAT =
'12/31/2014'
where
 JOBCOD =
'PH14789' 

I also want to include the following instead of running it one at a time

PH17523    
PH17524    
PH17525    
PH17553    
PH17555    
PH17556    
PH17557    
PH17558    
PH17571    
PH17573    
PH17574    
PH17575    
PH17576    
PH17577    
PH1757

View 9 Replies View Related

Transact SQL :: Multiple Update Top On Commit Transaction Doesn't Work

Jul 10, 2015

I have this sql stored procedure in SQL Server 2012:

ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes]
@newBatches as dbo.CreateBatchList READONLY
, @productId int
, @cLevelRatio int
, @nLevelRatio int
AS
set nocount on;

[Code] ....

View 4 Replies View Related

Transact SQL :: How To Update Multiple Rows In Different Transactions In A Single Table

Jul 16, 2015

We have control table which will be useful whether we need to start the job or not. If we are starting the Job we will make it to 1.

Below is the Table Structure.

Table Name       IN_USE_FG
CUST_D                     0
PROD_D                     0
GEO_D                       0
DATE_D                     0

Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package. 

Now we are getting dead lock because we are using same table at a same time. Because we are updating different records. 

View 6 Replies View Related

Querying Multiple Tables Multiple Times

May 31, 2007

I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me? 

View 12 Replies View Related

Transact SQL :: Looping For 100k Times?

May 13, 2015

I have created approximately 200lines of multiline table function. getting table variable values as what I need satisfying all our business rules.

my question : is it okay in performance wise using loop i executed this function 100000 times and it took around 16mins.

View 10 Replies View Related

Transact SQL :: DATEDIFF Does Not Have To Calculated Numerous Times?

Sep 4, 2015

What is a more efficient way of doing the following such that DATEDIFF() does not have to calculated numerous times?

CASE
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 0 THEN '<= 0D'
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 0 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 7 THEN '> 0D AND <= 7D'
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 7 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 30 THEN '> 7D AND <= 30D'
 
[code]....

View 5 Replies View Related

Transact SQL :: Shift Analysis - CAST Using Times

Nov 9, 2015

I am doing some analysis on shifts and what I want to do is say if the time from a timestamp field is between 00:00 and 05:59 then the figures belong to the day before.  Eg.  We have a night shift that work 22:00 - 06:00.

Here is my coding which logically looks sound to me but when I run it, it has an error:

SELECTe.ID,
CAST(e.TimestampasDate)asEventDate,
CAST(e.TimestampasTime)asEventTime,
CASE
WHENCAST(e.TimestampasTime)BETWEEN'00:00:01'and'05:59:59'
THENCAST(e.TimestampasDate)-1
ELSECAST(e.TimestampasDate)
END
FROMIntouch.Event

View 5 Replies View Related

Transact SQL :: Update Multiple Table Referencing New Table Data

Aug 4, 2015

I have a table called ADSCHL which contains the school_code as Primary key and other two table as

RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.

I tried using Merge Update function not sure if this is going to work.

Update dbo.ADSCHL
SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code
FROM dbo.ADSCHL AD
INNER JOIN TESTCEP FD
ON AD.SCHOOL_NAME = FD.School_Name

View 10 Replies View Related

Transact SQL :: How To Generate Rows Certain Times With A Counter Column

Jul 10, 2015

How can I write it in SQL? I have a table  that column  A   has two values of   Product  and Cost...Where value of A is Product, I have to add 6 more rows, and add column b with values from 1 to 7 for each rows ( counter)Where value of A is Cost, I have to add 4  more rows, and add column b with values from 1 to 5 for each rows ( counter).

View 3 Replies View Related

Run A Task Multiple Times

Oct 20, 2006

I have a situation where I run the same taks multiple times during the execution. I would like to have one task which runs every time, instead of duplicating the task over and over 14 times in my script.

Basically, it is an audit log, which I set variables and then insert into a SQL table the variables.

I would like to do this:

Task1 ------Success-----> Set Vars -----Success--> Log
|
Task2 ------Success-----> Set Vars -----Success-| (do the Log task again)
|
Task3 -------Success-----> Set Vars -----Success-| (do the Log task again)
|
etc

This works, however, I have to duplicate Log over and over and over. No OR does not work, because it still only executes the Log task once.

Another option I thought of, but cannot find a way to implement is: Make the Log task "disabled" with no dependencies, then in the Set Vars script, enable and execute Log and disable again.

Any ideas?

View 8 Replies View Related

Rda.push Multiple Times?

Oct 14, 2007



Hello,

After doing some research it seems like you can only push the same table once using rda.push -- is this correct? If yes, are there any other alternatives for saving changes to the table back to SQL Server aside from merge replication?

One idea I am toying with is to pull the tracked table with 0 records, save changes to the tracked table, push, drop table and pull, repeating this process everytime I push the data. Wondering is somebody has any advice?

Thanks!

View 1 Replies View Related

Transact SQL :: Update Table From Another Table For Multiple Rows

Jun 10, 2015

Matrix table has ID column and data below.

ID        Flag     TestDate         Value     Comment                                                                  
111          2       12/15/2014     7.5             null
222         2            Null                10          received

Matrix_Current table could have 1 or multiple rows as below.

ID        Flag               TestDate           Value         Comment
111         2                  01/26/2015        7.9                                                                      
111         2                  02/23/2015        7.9                                                      
111         2                  04/07/2015        6.8
222        1                   null                   8               test comment 1
222        3                   null                   9               test comment 2

When I run below update

 UPDATE  AM
 SET  M.Flag = MC.Flag, M.TestDate = MC.TestDate,
M.Value = MC.Value, M.comment = MC.Comment
 FROM dbo.Matrix M inner join dbo.Matrix_Current MC on M.ID = MC.ID

Matrix table has value below:

ID        Flag     TestDate         Value     Comment                                                                  
111          2       01/26/2015      7.9             
222         1            Null               8            test comment 1

I want to update Matrix table from all row from Matrix_Current, final table would like below:

ID        Flag     TestDate        Value     Comment                                                                  
111          2        04/07/2015      6.8             
222         3            Null                9         test comment 2

View 3 Replies View Related

Transact SQL :: Check Result Of In Between Two Different Times In Week And Particular Time Period

Oct 16, 2015

I have a query to check the records of job has received after 4pm Monday to Friday and it has completed before 9am next day and also weekend it should be Friday after 4pm and before Monday 10am for particular financial year period. I have my job table which has full date but it doesn't show the date exactly Monday to Friday it shows only as 12-12-2014 like that.

View 2 Replies View Related

How To Connect To A DB Multiple Times Through Page

Dec 21, 2007

Hey all,
I am still pretty new to all of this and I am having problems accessing a the same DB twice in my page.  I want to pull information once in one spot, but then pull different information in a different spot on the page.  Anyway, in the first spot, I have the following code:
Dim conString As String = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString
Dim con As New SqlConnection(conString)Dim cmd As New SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con
con.Open()
Dim RS As SqlDataReader = cmd.ExecuteReader()
While RS.Read()
blah blah blah
End While
End Using
 
Then in my other spot on the page I have the following:
Dim cmdresults As New SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")
Using con
con.Open()Dim RD As SqlDataReader = cmdresults.ExecuteReader()
While RD.Read()%>
<tr>
<td><%=RD%>
</td>
</tr>
<%End While
End Using
 When I try to execute I get this error, "ExecuteReader: Connection property has not been initialized." on the following line, "
RD As SqlDataReader = cmdresults.ExecuteReader()"  Any ideas?  If possible a little explanation on how multiple connections to the same database work would be nice just for future reference.
Thanks in advance!!,
Chris

View 5 Replies View Related

Linked Server Multiple Times

May 15, 2000

I need to create a linked server that can access more than one database.
I assume, the only wat this can be done by creating two separate links from the local server using Microsoft OLE DB Provider for SQL server. But the
problem is the Server Name. I cannot use the same servername twice and
if I use a name that is not an exisiting SQL server name I get an error
that "server not found".

So, how do I addlink the same server a mulitple times to access different databases in the server?

Thanks.

Ranjit

View 3 Replies View Related

Alaising The Same Table Multiple Times

Oct 18, 2004

To All--

First off, I'm new to SQL Server and apologize if this is a trivial question.

What I'm trying to do is alias the same table with 2 different names so that I may join them on two different fields.

Table1
Emp_Number
Emp_Code_1
Emp_Code_2

Table2 (Contains a list of codes and their related descriptions)
Emp_Code
Long_Desc
Short_Desc

I'm trying to query Table1 for the Emp_Number but I want to get the Short_Desc from Table2 for both Emp_Code1 and Emp_Code2. I'm using Microsoft Access as the front end (using Pass Through Queries) and SQL Server on the back end.

Hope this makes sense.

C

View 1 Replies View Related

Running The Same Subquery Multiple Times

Mar 2, 2005

Hi,

I was wondering if this can be done...

I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?

E.g.
I have a table with 2 cols - amount, date.

SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)

Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.

As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?

This is only a basic example, what I'm trying to do will invovle a lot more calcultions.

Hope someone can help,
Thanks,
Stuart

View 5 Replies View Related

Same Field On Same Line Multiple Times

Apr 4, 2008

I'm new to SQL Reporting Services but have made some decent headway. I'm stumped by one issue though: How do I repeat a field over and over on a single line?

To elaborate:

I have a table of backup job failures, with a reason and the time it failed.
ID | Reason | Time
1 | No tape | 3/13/3008
2 | Bad drive | 3/14/2008

I'm trying to create a summary, and I want to list the information as follows:

There were 2 failed backup jobs on 3/13/2008, 3/14/2008

My SQL query is as follows:
SELECT Time, COUNT(*) AS NumFailures FROM FailedBackups GROUP BY Time ORDER BY Time ASC

How do I "loop" the Time field on a single line in Reporting Services?

View 6 Replies View Related

Transact SQL :: Best Way To Make A Function For Summing Arbitrary Number Of Times Values

Jun 22, 2015

How is the best way to make a function for summing an arbitrary number of times values (table parm?)- I 've read it's necessary to convert to seconds, sum then convert back, but Im' wondering if there's an alternative.

Here's the example I want to sum:
00:02:01:30
00:01:28:10
00:01:01:50
00:06:50:30
00:00:01:50

View 8 Replies View Related

Running Stored Procedure Multiple Times

Jun 25, 2007

I’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?
newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure
Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam)
ownrParam.Value = OwnerID
colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close()
colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close()
and so on for 9 columns…

View 7 Replies View Related

Calling A Single UDF Multiple Times Within 1 View

Sep 27, 2005

Lets say I have a User Defined Function. I need to call the UDF a number of times within the same View. However, the results are not as expected. The only way (I know of) is to have seperate instances on the same function saved under different names. Is there a way to call a single UDF multiple times within a View without the returned values being messed-up?

View 3 Replies View Related

Calling Stored Proc. Multiple Times...

Feb 1, 2008

I have basic SQL query that returns a one column result set. For each row returned in this result set, I need to pass the value in the column to a stored procedure and get back a result set.

I have 2 solutions, neither of which are very elegant. I'm hoping someone can point me in a better direction.

Solution 1:
Use a cursor. The cons here, are the SP returns a result set on each pass which generates multiple result sets overall. If there is a way to combine these result sets, I think this solution might work.

Solution 2:
Use a temp table or table variable.
The cons here are, if the schema of the result set returned from the stored procedure changes, the table variable will have to change to accommodate it. This is a dependency I'd rather not create.

Any help is very much appreciated.

View 2 Replies View Related

SQL 2012 :: SSIS XML Import Multiple Times A Day

Aug 1, 2014

I am importing xml multiple times a day from a vendor. However when SSIS created the ID's for nested XML data it is not unique. So importing the first time and I get 3-4 records it looks fine. However subsequent imports all use the same ID's so it isn't unique, how do I go about changing this as I cant find anything about it.

View 7 Replies View Related

DB Engine :: Trigger Is Firing Multiple Times

Apr 23, 2015

we have a table in database , which has multiple triggers defined by Vendor. I have created our own custom trigger on this table also , which fires last. Goal of this custom trigger is to send an email, when ever update happens on table. But somehow trigger is firing multiple times for same update, so it is sending multiple email for same update also

View 7 Replies View Related







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