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
ADVERTISEMENT
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
Oct 22, 2015
Every day the timestamp is changed on all rows in one of the table(table has 1 mio records).
What is the best way to find out which query is doing this?
Could be also query which updates only one record(or couple of them) but is executed for each record once.
I have been looking sys.dm_exec_query_stats and sys.dm_exec_sql_text for past but couldn't find.
I can't use event notifications since there is service broker disabled.
View 5 Replies
View Related
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
Nov 6, 2015
I have 2 tables A, B with 1 to many relationship
Table A(ProductID), TableB(ProductID, FileID)
I need to find only the records in Table A that may have more than one FileIDs in Table B, since some ProductIDS have multiple FileIDs in Table B...
View 8 Replies
View Related
Nov 19, 2015
I want to update the STATUS column based on min date in my table.
For example – If minimum BOOKING_DATE of any RecieptID is below to 2015-10-01, then Status of that RecieptID should be 0 for all records pertaining to dedicated RecieptID So I need an output in this way.
View 3 Replies
View Related
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
Jun 10, 2015
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE
TABLE [dbo].[User]
(
[UserId]
[bigint] NOT
NULL
,[country]
[int] NULL
-- User’s home country
,[name]
[nvarchar](15)
NULL -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]
[int] NULL
)
-- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
EventId UserId TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1 1 1000 2010-10-16 00:19:56.460
2 1 1500 2010-10-16 00:20:56.460
3 1 3000 2010-10-16 00:21:56.460
4 1 10000 2010-10-16 00:29:56.460
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute)
John 2010-10-16 00:21:56 1500
Dave 2010-10-16 00:30:50 3200
Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
View 3 Replies
View Related
Sep 5, 2015
How to update tblA with records from tblB (see below expected results) when they have common column DrawingNo?
View 4 Replies
View Related
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
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
Apr 26, 2015
I have two tables A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.
View 5 Replies
View Related
Oct 21, 2015
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
View 3 Replies
View Related
Aug 13, 2015
I have following query which is created dynamically as -
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1889147436' AND SEND_DATE = '1941-03-04';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1115509374' AND SEND_DATE = '1991-09-01';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1515579671' AND SEND_DATE = '1941-05-24';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1795509670' AND SEND_DATE = '1958-01-14';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1915508672' AND SEND_DATE = '1961-09-07';
Here till " UPDATE BUILDTABLE SET BUILD_ID = '984137'" is the same clause for all queries, but "where" condition is different for all queries. I have to update more than 500 UPDATE statements(like above) in one call. Currently I am concatenating all the queries in string Builder which is time consuming.I want to increase performance of application.Any other class like BulkCopy ?
View 18 Replies
View Related
Aug 30, 2015
This is a bit lengthy, but lets say we have three tables
a) tblSaleStatementCustomer
b) tblCreditors
c) tblReceiptDue
which shows records like below
Table 1 - tblSaleStatementCustomer
ID CustomerName VoucherType Outbound Inbound CustomerType
----------------------------------------------------------------------------------------------
1 ABC Sales 10000 0 Dealer
2 MNC Sales 9000 0 Dealer
3 MNC Sales 4000 0 Dealer
Table 2 - tblCreditors
ID Name OpeningBalance
----------------------------------------------------------------------------------------------
1 ABC 20000
2 MNC 15000
3 XBM 18000
4 XYZ 12000
View 2 Replies
View Related
Oct 21, 2015
I am looking out for sample stored procedures returning multiple records
Example: GetOrderDetailsByOrderId
The above stored procedure should take orderId as parameter and should return the the order details along mutiple line item details.
View 4 Replies
View Related
Sep 2, 2015
updating the # of Payer from below query to match with the # of rows for each payer record. See the Current and desired results below. The query is currently counting the # of rows for all payers together and updating 3 as # of payers. I need it to count # of rows for each payer like shown inDesired result below. It should be showing 1 for first payer and 2 for 2nd & 3rd based on # of times each payer is repeated..
SELECT b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN,
(Select Count(*) From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c where a.CLAIM_ICN_NU =
c.CLAIM_ICN and b.TPL_TS = c.TPL_TS and a.CLAIM_TYPE_CD = 'X'
[Code] ....
Current Result
FILING_IND
PYR_CD
PAYER_ID
PAYER_NAME
CLAIM_ICN
#_OF_PAYER
[code]....
View 4 Replies
View Related
May 21, 2015
I have a CTE returning a recordset which contains a column SRC. SRC is a number which I use later to get counts and sums for the records in a distinct list.
declare@startdate date = '2014-04-01'
declare@enddate date = '2014-05-01'
; with SM as
(
SELECT --ROW_NUMBER() OVER (PARTITION BY u.SRC ORDER BY u.SRC) As Row,
u.SRC,
[Code] ....
-- If Referral start date is between our requested dates
ref.Referral_Start_Date between @startdate and @enddate
OR
-- Include referrals which started before our requested date, but are still active during our date range.
(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL ))
)
INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id
AND s.Service_Delivery_Unit_Id = 200
)
SELECT
count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,
[code]....
View 5 Replies
View Related
Aug 7, 2015
I have a single complex query.
SELECT
Col1, -- Header,
Col2, -- Header,
Col3, -- Detail
Col4, -- Detail
Col5, -- Detail
FROM
TableName;
The query repeats the Header row value for all children associated with the header.I need the output of the query in XML format such that..For every Header element in the XML, all its children should come under that header element//I am using -
SELECT
Cols
FROM
Table Names
FOR XML PATH ('Header'), root('root') , ELEMENTS XSINIL
This still repeats the header for each detail (in the XML) , but I need all children for a header under it.I basically want my output in this format -
<Header >
<detail 1>
</detail 1>
<Detail 2>
</Detail 2>
<detail 3>
</detail 3>
</Header>
View 2 Replies
View Related
Jan 7, 2015
I have a question, what kind of Query or function do I have to use to UPDATE multiple record that I just want to add a letter at the end of the existing OrderNum.
Before
ID NAME OrderNum
1 Pete WEB123
2 Paul WEB124
3 Sam WEB125
4 Tim WEB126
After
ID NAME OrderNum
1 Pete WEB123A
2 Paul WEB124A
3 Sam WEB125A
4 Tim WEB126A
View 3 Replies
View Related
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 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
Mar 3, 2004
Got a beginner question here...
Let's say I have a database table that houses server information with four columns: make, model, serial #, ip address. And assume there are ten rows with that information filled out. How could I display all the rows of information on a single webpage (ASP.NET), with all the fields being editable; and a single save button that would send any changes to the database (in reality I guess it would be sending all rows and fields to the database, and just overwrite the previous data).
Could a page such as that be created using FrontPage 2003 or Dreamweaver MX 2004?
This would be strictly for updating information. I would have a separate form for adding a new entry.
Thanks for your help.
View 1 Replies
View Related
Feb 13, 2008
I have an 'update' query that looks like this:
update wce_contact
set blank = 'missing'
where website in ('www.name1.co.uk','www.name2.co.uk','www.name3.co.uk')
I know this query will set 'blank' to missing when it matches the above websites. However if i wanted to set blank to 'missing' where mail1date is not null and mail2date is not null (keep going to mail18date not null) how exactly would i go about this?
I guess it would be a case of adding another bracket somewhere but im unsure?
View 3 Replies
View Related
Feb 26, 2008
Hi All,
I want to know that how we will be able to update multiple rows in single transaction.
e.g If original database is
S_No Data
1 -
2 -
3 -
4 -
After
S_No Data
1 1
2 3
3 6
4 10
View 4 Replies
View Related
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
Aug 7, 2015
I have a table with 1 million records. I want to update only 400 records. The update statement is provided by a 3rd party vendor. Once i run the update statement it will update all the 400 records. Once the table is updated the users will validate the table
if the update is successful or not. What i'm looking for is:
1) Is there a way to identify what records were updated.
2) If the update done is not what the users wanted i need to undo and bring back the 400 records to their previous values.
I'm on sql server 2008.
View 34 Replies
View Related
Jan 16, 2007
I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101;
UPDATE HashedPassword = someOtherValue WHERE UserID = 102;
...
What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery().
Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx
'2. Create a command object for the query
Dim strSQL as String = _
"INSERT INTO UserAccount(Username,Password) " & _
"VALUES(@Username, @Password)"
Dim objCmd as New SqlCommand(strSQL, objConn)
'3. Create parameters
Dim paramUsername as SqlParameter
paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25)
paramUsername.Value = txtUsername.Text
objCmd.Parameters.Add(paramUsername)
Dim paramPwd as SqlParameter
paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16)
paramPwd.Value = hashedBytes
objCmd.Parameters.Add(paramPwd)
'Insert the records into the database
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
View 1 Replies
View Related
Mar 25, 2015
I have two tables that can be created with sample data using the DDL at the bottom of this post. What I'm looking to do is update the QtyReceived column in tblPurchaseOrderLineDetail from the Qty column in tblReceivedItems. However, the tricky part that I can't figure out is splitting these quantities out over multiple lines. I should only be allowed to receive up to the QtyOrdered column in tblPurchaseOrderLineDetail.
For a specific example from the sample data we'll look at PurchaseOrderDetailID 28526. From the tblReceivedItems, there are three records with quantities of 48, 48, and 20. From the tblPurchaseOrderLineDetail there are three records of QtyOrdered of 55, 45, and 20. What I would like to happen is fulfill the records in the tblPurchaseOrderLineDetail sequentially (essentially in order of ExpectedDate). So, the QtyReceived would be 55, 45, and 16 for the corresponding records. If there is already a quantity in the QtyReceived column, but it's less than the QtyOrdered column, the quantity needs to be added to the column (not overwritten).
DDL To CREATE Sample Tables and Data:
CREATE TABLE [dbo].[tblReceivedItems](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PurchaseOrderDetailID] [int] NULL,
[Qty] [int] NULL)
SET IDENTITY_INSERT [dbo].[tblReceivedItems] ON
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (1, 28191, 48)
[code]....
View 5 Replies
View Related
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
Jul 8, 2015
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
View 7 Replies
View Related
Jan 15, 2015
I have a After insert, update trigger. When I update multiple records with unique constraints column in it update fails. But if this a single record update it works.
Could like to know the reason.
View 9 Replies
View Related
