I have a table with a max key value in it, and another table with a few rows in it. I'm trying to update the two new rows with key values that are iterative from the MAX value in the first table. Could anyone point me to some good LOOP...UPDATE, etc resources or pointers before I go postal?
::grins::
Example: first table has a key field, last value in it is say 1000. The second table has two records. They need keys too, and those keys need to start at the last value in the first table + 1, so 1001, and end on the last row of the second table, or, say 1002. I cannot figure out how to read the last value of the first table, and create an update loop to iteratively update the key value in the second table based on the max value of the first, and looping based on the number of records in the second, in this case 2.
I have a table in SQL server which has two rows. One has an ID of 'Bag CL55412'. Another has an ID of 'Bag CL55412-Cpy'. The Price for the first one is $99.99. I want to make the price for the second one 2% more & $.99 more.
The data looks like this IDChannelPriceCompanyID Bag CL5541299.99111 Bag CL55412-Cpy102.99500
The SQL to select that formula looks like this.
SELECT [ID] ,[ChannelPrice] ,(CEILING([ChannelPrice]*1.02)+.99) ,CompanyID FROM [SC].[dbo].[Product] WHERE ID like '%CL55412%'
To Update, I can think of something like this, but it will update based on itself, not a different row in the table.
UPDATE [SC].[dbo].[Product] SET ChannelPrice=(CEILING([ChannelPrice]*1.02)+.99)
How would I get it to update as I want it to? The origin CompanyID will always be 111 & the destination company ID will always be 500 for all of the respective rows that need to be updated
Hi People, hope someone can help me out here with a little problem. Basically i've go a asp.net page which has a listbox on. This list box is populated from a SQL database table with the datetime of the a selected field. Thus in the list box you get a list of strings looking like this "24/09/07 12:58" Also on the page is a submit button, and some other editing textboxes. The main issue here is the when the submit button is used i get the currently selected listbox timedate string and then pass this along with other items to update a record in the database based on the datetime in the listbox control.
Below is how i get the string from the listbox control Dim except_time As DateTime except_time = DropDownList1.SelectedValue The expect_time is then passed to store procedure along with some other vars, which looks like this -- =============================================-- Author: Lee Trueman-- Create date: 1st Sept 2007-- =============================================CREATE PROCEDURE [dbo].[spExcept_UpdateData] -- Add the parameters for the stored procedure here @validated bit, @update_time datetime, @except_time datetimeASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON -- Insert statements for procedure here UPDATE exceptions SET validated = @validated, update_time = @update_time WHERE (except_time = @except_time)END So validated and update_time should be updated when except_time is equal to @except_time
My problem is that the database never updates. If i debug the asp.net page the watch var shows the datetime in US format (I.e "09/24/07 12:58"), if this is true then this would explain why when it is passed to the stored proc nothing gets updated, as there would not be a date match. can anyone see any silly mistakes i'm doing here ????
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.
Update CurrRank = ??? from tblAppKitOffers where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D') ORDER BY tblAppKitOffers.OfferAmount Desc
Currently working on a upload module where in the data from excel file is imported to the destination tables. Data in the excel sheet comes in phases. All excel sheet columns data don't come at first shot. The excel sheet's data is dumped into temporary tables which inturn is looped using cursor's and gets finally updated to the actual tables.
Now, the problem I am facing is how do I update columns of the actual table with the data (i.e NON NULL values) available in the temporary table without tampering the data allready present in actual table.
Ideally what required is, update the actual table column values with the corresponding columns of temporary table ONLY for Non NUll column values of temporary table.
Temporary and Destination tables have 85 columns each. I don't want to write 85 update queries.
The scenario which I am facing is given below with 2 columns as an example.
1. Table 1 :- tbl_source (Temporary Table) has two columns src_Col1 & src_Col2 2. Table 2 :- tbl_destination (Actual Table) has two columns dest_Col1 & des_Col2
Scenario -1 ---------------
tbl_Source Sample Data (after excel import to the temporary table) ------------------------
src_Col1 src_Col2 ------------------ 50 NULL
tbl_Destination Sample Data ------------------------
dest_Col1 dest_Col2 ------------------ 50 NULL
Scenario -2 ---------------
tbl_Source Sample Data ------------------------
src_Col1 src_Col2 ------------------ NULL 100
tbl_Destination Sample Data ------------------------
I am trying to figure out if what i am attempting to do is possible and whether or not my approach is wrong to begin with.
I am trying to build a custom report for our accounting system which is Traverse from Open systems. This is what i have done in the stored procedure thus far
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
ALTER PROCEDURE rptArFLSalesByCustItemized_sp @custId pCustID, @dateFrom datetime, @dateThru datetime, @itemIdFrom pItemId, @itemIdThru pItemId as set nocount on
-- define some variables for previous year declare @LYqty int, @LyAmt money, @LYfrom datetime, @LYthru datetime
-- set defaults SET @itemIdFrom=ISNULL(@itemIdFrom,(SELECT MIN(itemId) FROM tblInItem)) SET @itemIdThru=ISNULL(@itemIdThru,(SELECT MAX(itemId) FROM tblInItem)) SET @LYfrom=DATEADD(YEAR,-1,@dateFrom) SET @LYthru=DATEADD(YEAR, -1, @dateThru)
-- create small temp table to hold customer info Create Table #tmpArCustInfo ( custId pCustID, custName VARCHAR (30), ) -- populate customer temp table with info Insert into #tmpArCustInfo select custId, custName from tblArCust WHERE custId = @custId
-- create a temp table to hold the Data for each Item Create Table #tmpArSalesItemized ( itemId pItemId, productLine VARCHAR (12), pLineDesc VARCHAR (35), descr VARCHAR (35), LYQtySold int, LYTDQtySold int, QtySold int, LYTDsales money, totalSales money, LastInvDate datetime, )
-- populate the temp table with all of the inventory items insert into #tmpArSalesItemized select ii.itemId, ii.productLine, ip.Descr, ii.Descr, 0,0,0,0,0, NULL from tblInItem ii, tblInProductLine ip where ip.productLine = ii.productLine AND ii.itemId BETWEEN @itemIdFrom AND @itemIdThru
-- update table with this years quantities update #tmpArSalesItemized SET QtySold = (select SUM(QtyOrdSell) from tblArHistDetail hd where TransId IN (select TransId from tblArHistHeader where custId = @custId) AND orderDate IN (select OrderDate from tblArHistHeader where OrderDate BETWEEN @dateFrom AND @dateThru) AND hd.partId BETWEEN @itemIdFrom AND @itemIdThru GROUP BY hd.partId )
-- Return the temp tables results select * from #tmpArSalesItemized, #tmpArCustInfo
drop table #tmpArSalesItemized, #tmpArCustInfo
return
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
My problems begin where i want to start updating all of the Qty's of the QtySold field. I have managed to get it to write the same sum in every field but i cannot figure out how to update each row based on the sum of the qty found for that item in the tblArHistDetails table, trouble is too that there is no reference to the custId in that table either. The custId resides in tblArHistHeader and is linked to the details table via the TransId column. So really i need to update many rows based on criteria from 2 other tables.
Can anyone please help? I dont have a clue how to make this work, and most of what i have learned about sql thus far has been from opening other stored procs etc in the accounting system and just reading to see how the developers have done things.
I am trying to update records based on the results of a query with a subquery.
The result set being produced shows the record of an item number. This result produces the correct ItemNo which I need to update. The field I am looking to update is an integer named Block.
When I run the update statement all records are updated and not the result set when I run the query by itself.
Below is the code I am using in an attempt to update the block column but it updates all records and not the ones which I need to have the Blocked field set to 1.
Update #items set Blocked = 1 Where Exists ( SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END) --,SearchNo --,COUNT(*)
[Code] ...
Why is the update changing each record? How can I change the update to choose the correct records?
CREATE TABLE EDI_data_proc_log( ID int IDENTITY(1,1), comment VARCHAR(3000), time_recorded DATETIME DEFAULT GETDATE(), run_by varchar(100), duration int );
When a record is inserted I like the duration column to be computed.This should happen only after the first record to the table has gotten inserted.You might say a trigger would be the best.. Ok then, show me the syntax.
Or I am thinking can we write a user defined function that will compute the value for the duration column.
--By default, I would like to update the duration column as follows:
--It should record the number of seconds between the last insertion ( You can get that time from the time_recorded column from the previous record and the current time can be obtained from the getdate() function )
Dear All I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
I have an application that needs to create invoices on a daily basis to multiple clients based on orders shipped that day. This is easy to do on the front-end. But how can I do this on the back end in SQL Server. I want to sort orders by Client ID and put all orders belonging to one customer on one invoice. When customer id changes, I change the Invoice ID. Is this possible in SQL? Xcog
Hi there, I need to create a stored procedure that I can pass in an arbitrarily long list of record IDs and have an SQL statement executed for each one. For instance, I want to be able to pass in 1, 2, 5, 78, 100, 216 and have it automatically execute: UPDATE table SET value='blah' WHERE id=1; UPDATE table SET value='blah' WHERE id=2; UPDATE table SET value='blah' WHERE id=5; UPDATE table SET value='blah' WHERE id=78; UPDATE table SET value='blah' WHERE id=100; UPDATE table SET value='blah' WHERE id=216 Can anyone please show me how I would structure a stored procedure like this? Specifically, how would I pass the list of IDs in as a parameter, and how would I iterate through them. Much appreciated!!
A Stored Procedure inserts a record in a base table. I want to add n records (n=1 to 5) into a related table. Another SP (tdAuthorityInsert) is already set up to insert one record into the related table.
So, two questions 1. What is the form of an iterative loop in a Stored Procedure? 2. How do you call a Stored Procedure from inside another?
I populate the first table with a litst of stores that offer all desired items.
I populate the second table with a list of vendors, the item is, and cost avaiable at each of the stores in the first table.
What I would like is to output all possible the store and vendor combos ordered by combined price.
So, for instance, I have 3 products, A B and C. Store X has A and B by vendor G, and A B and C by vendor H. I want the output to have all iterations of (Store, Product, Vendor, Price) grouped in order of total price. So...
X A G X B G X C H
X A G X B H X C H
X A H X B G X C H
X A H X B H X C H
ordered by each group's combined price.
For some reason, I can't get this straight in my head. Must need more coffee.
I have a synchronous script component and have added 5 fields to the output (field1, field2... field5). Can I iterate those fields?
In sudo language, I'd like to do:
Code Snippet
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) dim i as integer = 1 while i < 6 row.field[i] = "somevalue: " + i.ToString end while end sub
Somehow, I'd like to be able to do that without specifying each field individually:
Can I update a select query using sql server 2000? What I mean is, I want to use a query instead of table or a view.
eg update (select * from tablename) set field='value'
something of this sort. I tried doing some r&d but it didn't work. I want it as single query. If some one knows it please let me know. It is very urgent.
I 've have a stored procedure that compares fields across databases.In order to do so it requires 2 values it acquires from 2 tables. Thesearch is based on the ID of the data owner and a subject:proc_evaluate_results @StudentId = '222222', Course = 'PSY101'In order to obtain those values I run a cursor accross my records andSELECT THEM INTO 2 @variables, which then replace 222222 and PSY101with dynamic values eg.--define a cursor etc.etc.WHILE @@cursor_fetch = 0BEGIN--do the cursor call INTO @varsEXEC proc_evaluate_results @StudentId = @studentID, @Course =@CourseCodeENDNow,the vars are being passed to the stored procedure and executed OK,but the cursor gets stuck on the last record and continues to evaluateit until stopped manually.If I comment out the EXEC and replace it with eg. PRINT @Course + ' |' + @CourseCode it runs fine, exiting after the last record.ThanksR>
Hi, I'm very new to stored procedures and I've been searching on google to find a way to custom page my results using SQL Server 2005. I'm trying to use Row_Number() and a CTE to keep things efficient and scaleable but I keep getting an error next to my UPDATE statement. I get the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'UPDATE'." The sproc works without the UPDATE statement. Does anyone know where I need to put the UPDATE statement to update the "searched" field for each record selected?
CREATE PROCEDURE [zk_update_request_england](@property_type tinyint,@market_status tinyint,@price int,@bedrooms tinyint,@search_location varchar(30),@search_district varchar(30),@PageSize int,@PageIndex int) AS BEGIN WITH SearchResults AS( UPDATE dbo.zk_request_england SET searched = searched + 1 WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price SELECT user_id, min_price, max_price, property_description, searched, ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber FROM dbo.zk_request_england WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price ) SELECT user_id, min_price, max_price, property_description FROM SearchResults WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize END
Hey all, I'm having an interesting problem, but also keep in mind I am certainly a newbie with DB's and SQL but here's what I got:
I'm making an application that creates quotes for our customers. I want to create a Quote Number that is based on the largest of the last number in the DB but only based on that company. For example:
CoID QuoteNum 123 1 123 2 666 1 123 3 666 2 666 3
so if I created a new quote for CoID: 666, the QuoteNum would be 4. Easy enough. But the problem arises when I create a new quote for a NEW COMPANY, one who's CoID is not already present in the DB. So what I have so far is this:
SELECT QuoteNum FROM Quote WHERE QuoteNum = (SELECT MAX(QuoteNum) FROM Quote WHERE CoID='222')
but since 222 does not yet exist I get an error: "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."
So how do I tell the DB to ignore that error and perform another task should it arise? Any help would greatly be appreciated. THANKS!
I am trying to move files to directories based on the file status. If the file contains the Flag='E' then I want to move it to an "Error" folder, otherwise it will go to a "Processed" folder.
Here is my current code:
DECLARE @cmd varchar(500) SELECT CASE WHEN Processed_Flag = 'E' THEN SET @cmd='MOVE /Y C: empmyfiles*.dat C: empmyfilesError' ELSE SET @cmd='MOVE /Y C: empmyfiles*.dat C: empmyfilesProcessed' END FROM#TEMP
How do I change the color of the task icon back to green? I have and FEL with tasks in it that occessionally fail. The error is trapped to allow the container to continue processing. I would like to change the color of the icon back to green on the next successful iteration of a task but I haven't found a way to do it.
I need to select certain rows based on a "datetime" column. I need to select rows from 8am yesterday until 8am today. In Oracle I would use: select * from foo where TIMESTAMP >= trunc(sysdate - 1) + 8/24 AND TIMESTAMP < trunc(sysdate) + 8/24. This would start at 8am yesterday and end at 7:59am today.
I have a table with two columns: value and weight (weight is smallint).
I want a query that will return the value of one row at random with the added requirement that rows with a higher weight have a higher probability of being chosen. Any ideas?
the following criteria. i have the selection all done but am trying to figure out how to do the following: if column4 < 0 then add column4 to column3, move 0 to column4; if column3 < 0 then add column3 to column2, move 0 to column3; if column2 < 0 then add column2 to column1, move 0 to column2; add column3 to column4; move column2 to column3; move column1 to column2; if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;
