Update The Selected Rows Before Returning Them In One Command.
Mar 14, 2008
Hello,
I have an external process that polls message rows from a table. apon taking them, it also needs to mark them as taken. there is a status column in the table. marking messages as taken will change the value of status.
How can i perform both these operations in one command? Select the top x rows where their status is equal 1, then update the status of those same rows to a value of 5 for example.
I could iterate through the result of hte intial select and change the status 1 by 1 using a cursor, but this seems like a slow option.
Any other ideas?
Thank you.
View 4 Replies
ADVERTISEMENT
Jun 13, 2002
Say you have a table that contains 1000 rows, you create a simple select statement with a where clause that returns 100 or so of those rows. Easy enough.
Now, let's say that you wish to modify that select so that out of the 100 rows that match the where clause criteria, you only wish to return 10 rows randomly (i.e., you could run this query multiple times and get different results). How exactly would you go about doing this, efficiently?
I've thought about creating a stored procedure that will query the initial 100 rows into a temp table with an additional column (to number the rows from 1 to 100). Then setting up a loop (10 iterations) that will then generate a random number from 1 to 100 and select that row number into another temp table. At the end of the loop I'll have my table of randomly selected records. I am sure there is probably a better way to do this...
~Mike
View 2 Replies
View Related
Apr 11, 2004
Hi,
i read from help files that "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. " Anyone know how to get the return value from the query below?
Below is the normal way i did in vb.net, but how to check for the return value. Please help.
========
Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As SqlConnection)
Dim myCommand As New SqlCommand(myExecuteQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub 'CreateMySqlCommand
========
Thank you.
View 12 Replies
View Related
Jul 10, 2014
I am trying to run a query against the time & resourceorganization tables that returns selected entries from the time table, the date of the first day of the work week, and the current department. The query returns an "Exists" error. When I add exists or where exists I get ..an error near From. The main query and the sub query each run by themselves I just can;t seem to combine them. Here is what I have:
SELECT Time.ResourceID, DATEADD(dd, -(DATEPART(dw, Time.TimeEntryDate)-1), Time.TimeEntryDate) [WeekStart], Time.TimeEntryDate,
Time.ActivityCode, Time.ProjectCode, Time.TaskName, Time.StandardHours,
Time.StatusCode,
[Code] ....
View 7 Replies
View Related
Apr 3, 2008
i have a stored procedure that has parameters. this dataset is used in a crystal report. the parameters are in the sp and will return records if a value is selected. i would like to return records if one parameter or all parameters are selected.
there are 3 parameters, date range, receipt, po #
if i select a distinct value and leave the others null, or 2 values 1 null etc, i would like to return the records, i am having trouble with the syntax. thank you
View 1 Replies
View Related
Feb 15, 2007
How does one return all selected values in a multi-valued parameter? Right now i have a filter on the dataset where
(Expression)
=Fields!LOCATION_ID.Value
(Operator)
=
(Value)
=Parameters!Loc.Value(0)
This is just giving me data from the first value that is selected in the multi-valued dropdown. I need all returned from the parameter. Any ideas.
View 4 Replies
View Related
Dec 12, 2014
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
View 5 Replies
View Related
Oct 13, 2006
I have to dts rows by timestamp. For example if my dts downloaded at 10 am then in the next run i want to grab rows updated in the AS400 after 10am. what is the best way to go in ssis?
thanks...
kushpaw
View 5 Replies
View Related
Feb 23, 2007
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
View 2 Replies
View Related
May 27, 2005
This is my SQL :
Select p.patientid,p.patientname,p.patientIc,pvi.DateOfAdmission,pvi.visitid,pvi.ward,pvi.bedno,pf.status,pvi.SurgeonName,(f.Title + ' ( Ver ' + (CAST(f.Version as Char(10))) + ')') as Title FROM patient p, patientvarianceinfo pvi,patientForm pf,Form f where (p.PatientName LIKE '%" & Name & "%' or p.PatientIc LIKE '%" & ic & "%' or pvi.Ward LIKE '%" & ward & "%' or pvi.Bedno LIKE '%" & bed & "%') and (p.patientid = pvi.patientid) and (p.patientid = pf.patientid) and (pvi.patientid = pf.patientid) and (pf.FormID = f.FormID)and p.patientid in (select patientid from patientform pf)how do i get the number of rows?
View 1 Replies
View Related
Apr 25, 2001
I want to show a row number for each row returned by a SELECT starting at 1 and incrementing by 1 for each row.
Anyone got any ideas?
View 2 Replies
View Related
Nov 9, 2004
Hi All
by using this query
"select * from sample order by newid()" im getting a set of rows. On refreshing this query i need the same set of rows to validate.(provided sufficient data in the table).
Please provide me the query to use for this
Adv. Thanks
Hari...
View 2 Replies
View Related
Sep 11, 2006
Hi!I'm a new T-SQL developer and just hit a roadblock.I have a scenario that goes like this: I have 2 stored procedures,spInner and spOuter. spInner has a SELECT statement which wouldnormally be used by a class using MS Enterprise Library and that outputgoes into a DataSet. However, I need to get the output of the SELECTstatement to go into spOuter and that's what I can't seem to figureout.I know I may be asked to use functions that return tables in replies tothis post, but I can't do that as some parts of my application have anEXEC(string) for dynamic SQL, instead of spInner.Any help appreciated.Cheers,N.I.T.I.N.
View 2 Replies
View Related
Nov 19, 2007
Hello,
I start with this table,
Tagname
Timestamp
Value
A
12/12/2007 12:00:01
1
A
12/12/2007 12:00:02
1,1
A
12/12/2007 12:00:03
30
B
12/12/2007 12:00:01
12
B
12/12/2007 12:00:02
12
B
12/12/2007 12:00:03
12
C
12/12/2007 12:00:01
13,4
C
12/12/2007 12:00:02
14
C
12/12/2007 12:00:03
14,5
I would like the table to be as following
Timestamp
A
B
C
12/12/2007 12:00:01
1
12
13,4
12/12/2007 12:00:02
1,1
12
14
12/12/2007 12:00:03
30
12
14,5
Does anyone knows how to do this within a query?
Thx,
Nic
View 9 Replies
View Related
Apr 30, 2007
Hi,
I am having a problem updating one field in a table the update should be the product of two other fields from the same row.
There are atleast 3000+ records need to be updated here.
e.g.
update A
set A.b = A.c * A.D
here b c and d are from same row .. I was wondering if someone knows how to solve this problem.
Thanks in advance.
View 8 Replies
View Related
Aug 9, 2012
I am trying to just get a count of the number of distinct rows are in a table --- not looking at the entire row of fields, but only selecting a few.
i don't want to see the distinct rows, i just want a count of how many are in the table.
View 4 Replies
View Related
Nov 13, 2006
I am fairly sure that I am just overlooking something, but the following command is returning some incorrect fields.
SqlDataSource1.SelectCommand = "SELECT ITNBR, (SELECT ITDSC FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS ITDSC, SUM(MOHTQ) AS Balance, (SELECT VNDNR FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS VENDOR FROM AMFLIBT.ITEMBL WHERE VNDNR = @DDLVNDNR GROUP BY ITNBR, VENDOR"
SqlDataSource1.SelectParameters.Add("ddlvndnr", ddl1.SelectedValue)
I have more code to show, if this looks correct. Just let me know.
Thanks in advance.
View 2 Replies
View Related
Feb 24, 2007
Hello, can anyone see a problem with this T-SQL? 1 set ANSI_NULLS ON
2 set QUOTED_IDENTIFIER ON
3 GO
4 ALTER PROCEDURE [dbo].[Logon_P]
5 @User_ID VARCHAR(50),
6 @User_Password VARCHAR(50)
7 AS
8
9 IF EXISTS(SELECT 1
10 FROM [User]
11 WHERE [User_Name] = @User_ID)
12 BEGIN
13 RETURN 1
14 IF ((SELECT User_Password FROM dbo.[User] WHERE [User_Name]) = @User_ID) = @User_Password
15 BEGIN
16 RETURN 2
17 END
18 END
19 ELSE
20 RETURN 0
21 Its returning the following error:Msg 4145, Level 15, State 1, Procedure Logon_P, Line 11An expression of non-boolean type specified in a context where a condition is expected, near ')'.
View 3 Replies
View Related
May 26, 2000
Hi Guys!
This SQL statement, though carefully written to delete only selected rows, deletes the entire A_Shift_Times table:
DELETE FROM A_Shift_Times
WHERE EXISTS (
SELECT 1
FROM
Users
WHERE
(A_Shift_Times.time_in >= CONVERT(DATETIME, '2000-05-29 00:00:00', 102)) AND
((Users.User_Password LIKE N'mrr%') OR (Users.User_Password LIKE N'work%')))
What gives? What am I doing wrong here?
View 1 Replies
View Related
Feb 20, 2013
Recently I had an application developer approach me and asked if I could provided him with a list of sprocs by returned row count. We had an issue where the application passed in a number of parameters which attempted to return 200k plus rows of data and the application was timing out. He changed the required parameters in the application and a reasonable number of rows were returned as expected. Short term solution to this one problem.
However there are always timeout issues with this particular application and we got to thinking that maybe other sprocs that were called using parameters would also fail at some point in time because too much data was being returned.
View 5 Replies
View Related
Jan 24, 2008
I have to write a couple scripts that will update a couple columns in two separate tables and also insert a new row with the same data except for a few calculated or provided values ...... see specs below ...
1. tGradeHist Table Script One (Needs to be run first)
a. Read tGradeHist Table and Select rows with GradeEndDate = NULL and GradeStartDate = '1/1/2007 12:00:00 A.M.'
b. Calculate New Step Amount = StepAmount * Incr% (Round To Nearest Whole Dollar)
c. Create New Row for this table using information from row read above and insert new information where indicated :
GradeCode - Same
GradeLocationCode - Same
Step - Same
GradeStartDate - '7/1/2007 12:00:00 A.M.'
GradeEndDate - NULL
GradeCurrencyCode - Same
StepAmount - Result of b (above)
GradeFrequencyCode - Same
RangeMaximumAmount - Same
RangeMidAmount - Same
RangeMinimumAmount - Same
GradeCurrentFlag - 'True'
MarketMaximumAmount - Same
MarketMidAmount - Same
MarketMinimumAmount - Same
GradeGUID - Same
TSCOL - Same
d. Update Row read in a (above) with GradeEndDate = '6/30/2007 12:00:00 A.M.' and GradeCurrentFlag = 'False'
2. tPersonBasePayHist Table Script Two (Needs to be run second)
a. Read tPersonBasePayHist Table and Select rows with PersonBasePayEndDate = NULL
b. Calculate New PersonBasePayAmount = PersonBasePayAmount * Incr% (Round To Nearest Whole Dollar)
c. Create New Row for this table using information from row read above and insert new information where indicated :
PersonGUID - Same
PersonBasePayStartDate - '7/1/2007 12:00:00 A.M.'
PersonBasePayEndDate - NULL
PersonBasePayCurrencyCode - Same
PersonBasePayAmount - Result of b (above)
PersonBasePayFrequency - Same
PersonBasePayPayrollFrequencyCode - Same
BasePayReasonCode - 'SA'
ConductedBasePayReviewDate - Same
ScheduledBasePayReviewDate - Same
PayrollCode - Same
PersonBasePayCurrentFlag - 'True'
ApprovedByPersonGUID - Same
PersonBasePayGUID - Same
TSCol - Same
d. Update Row read in a (above) with PersonBasePayEndDate = '6/30/2007 12:00:00 A.M.' and PersonBasePayCurrentFlag = 'False'
View 7 Replies
View Related
Mar 28, 2006
I 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
View 7 Replies
View Related
Jun 21, 2006
The columns in my excel source contain data of different types with the column name being a string and the data in those columns being integers. Is there any way to only extract numeric data , in short I want column names to be omitted. Also the data is distributed unevenly , beggining at various rows in each column.
Thanking in advance :)
View 3 Replies
View Related
Oct 2, 2007
Hi,
Hope you guys won't mind this rather newbie question. I'm writing a simple blog page for my website and have created a SqlDataSource which queries the database for a list of blog post titles (from the web.Blog table) and the number of comments (from the web.BlogComments table). The SqlDataSource control is:
<asp:SqlDataSource ID="sourceBlogArticles" ProviderName="System.Data.SqlClient" connectionString="<%$ ConnectionStrings:myDatabase %>" runat="server" SelectCommand="SELECT gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded, COUNT(gbc.blogID) AS noOfComments FROM web.Blog gb LEFT OUTER JOIN web.BlogComments gbc ON gb.blogID = gbc.blogID GROUP BY gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded ORDER BY gb.dateAdded"></asp:SqlDataSource>
This works perfectly well if each blog entry in the web.Blog table has associated comments in the web.BlogComments table. However, if there are no comments yet defined in the web.BlogComments table for that blogID then no row is returned in ASP.Net (as checked with a GridView control or similar linked to the data source to view what I get)
HOWEVER, I think the SELECT command IS correct: if I use the select command as a query in SQL Server Managment Studio Express, I do get the rows returned, with 0 for the number of comments which is what I would expect for that query:
blogID, title, description, tags, dateAdded, noOfComments
1, title 1, description for title 1, tag1, 2007-09-27 06:49:03.810, 32, title 2, description for title 2, tag2, 2007-09-27 06:49:37.513, 03, title 3, description for title3, tag3, 2007-10-02 18:21:30.467, 0
Can anyone help? The result from the SSMSE query is what I want, yet when I use the very same SELECT statement in my SqlDataSource I don't get any rows returned if the BlogComment count is zero (in the above example I get only the first row). Many thanks for any suggestions!
View 6 Replies
View Related
Jun 21, 2012
I need to randomly order the selected rows from my table. Is this better to do on the Application level or in a stored procedure using "orderby NewID()"?
Which is faster? There will be about 100 rows returned with 10 columns.
View 5 Replies
View Related
Apr 4, 2007
Hi,I have an Excel file with 400 rows of old values and the correspondingnew values. My table currently has 10 columns out of which 3 columnsuse the old value specified in the excel file. I need to update thoseold values in the columns with the new values from the Excel file.Please guide me as to how to proceed with this.Thanks in advance!
View 4 Replies
View Related
Mar 18, 2005
hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
View 2 Replies
View Related
May 6, 2015
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
View 3 Replies
View Related
Oct 22, 2001
Greetings!
I need to run a select statement that only returns 50 rows. How do I limit the amount of rows returned? Normally the query will return hundreds of rows but all I need is the first 50 it retrieves. I have looked in the BOL and can only find help with a block cursor not just a query.
Thanks a million
zachary
View 1 Replies
View Related
Mar 7, 2008
Using MS SQLServer 2000
Is there a way to create a query that will return only 1 row within a join for example:
select a,b,c,d
from tbl1
inner join tbl2 on top 1 tbl2.a = tbl1.a <-- return only the top record here...
I dont have too much of a problem with joins... however I was wondering if there was a mechanism to just specify what would be the top most record from a join clause.
thanks
Kevin
View 1 Replies
View Related
Jun 30, 2006
I have a query set up that returns the data that I would like, but Iwould only like the latest data for each vehicle number. The query Ihave set up isSELECT TOP 100 PERCENT dbo.vwEvents.EventName,dbo.luSessionAll.SessionName, dbo.luOuting.OutingNumber,dbo.luVehicle.VehicleName, dbo.luOuting.OutingID,dbo.tblOutings.OutingStartTime,dbo.tblSessions.Ses sionDate,dbo.tblSessions.SessionStartTimeFROM dbo.vwSessions INNER JOIN dbo.vwEvents ONdbo.vwSessions.Event = dbo.vwEvents.EventIDINNER JOINdbo.luSessionAll ON dbo.vwEvents.EventID =dbo.luSessionAll.Event INNER JOINdbo.luOuting ON dbo.luSessionAll.SessionID =dbo.luOuting.SessionID INNER JOINdbo.luVehicle ON dbo.luSessionAll.Vehicle =dbo.luVehicle.VehicleID INNER JOINdbo.tblOutings ON dbo.luOuting.OutingID =dbo.tblOutings.OutingID INNER JOINdbo.tblSessions ON dbo.tblOutings.[Session] =dbo.tblSessions.SessionIDGROUP BY dbo.vwEvents.EventName, dbo.luSessionAll.SessionName,dbo.luOuting.OutingNumber, dbo.luVehicle.VehicleName,dbo.luOuting.OutingID, dbo.tblOutings.OutingStartTime,dbo.tblSessions.SessionStartTime, dbo.tblSessions.SessionDateORDER BY dbo.luVehicle.VehicleName, dbo.tblSessions.SessionDate,dbo.tblSessions.SessionStartTime, dbo.tblOutings.OutingStartTimethis returns all the outings. I would like the outing that has, inorder of importance, the latest session date, latest session time andlatest outing start time. Outing start time can sometimes be <<Null>>but the other two always have values. How would I go about doing this?thanks in advance for any help
View 2 Replies
View Related
Feb 21, 2014
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
View 0 Replies
View Related
Oct 19, 2005
UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?
View 1 Replies
View Related
