New Values Ignored In SQL Server UPDATE Query
Nov 4, 2004
I am new to both ASP.net and this forum. I have seen some posts close to this, but none address this problem.
I have a SQL Server database on JOHN1 called 'siu_log' with a table called 'siu_log'. It has two fields: Scenarios char[20] and Machines char[20].
I have been adapting code from Build Your Own ASP.NET Website in C# & VB.NET by Zac Ruvalcaba to learn the language. Much of what you will see is his work adapted for my use.
First, the html:
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="SIU_Assign.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>SIU Interface Scenario Assignments</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<asp:datagrid id="scenariosDataGrid" runat="server" CellPadding="4" AutoGenerateColumns="False"
OnUpdateCommand="dg_Update" OnCancelCommand="dg_Cancel" OnEditCommand="dg_Edit" DataKeyField="Scenarios">
<ItemStyle BackColor="#00DDDD" ForeColor="#000000" />
<HeaderStyle BackColor="#003366" ForeColor="#FFFFFF" />
<Columns>
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" />
<asp:BoundColumn DataField="Scenarios" HeaderText="Scenario" ReadOnly="True" />
<asp:TemplateColumn>
<HeaderTemplate>
Machine
</HeaderTemplate>
<ItemTemplate>
<%# Container.DataItem("Machines") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtMachine" Runat=server Text='<%# Container.DataItem("Machines") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>
<asp:Label id="resultLabel" runat="server"></asp:Label></form>
</body>
</HTML>
Then the CodeBehind:
Sub dg_Update(ByVal s As Object, ByVal e As DataGridCommandEventArgs)
Dim strMachineName, strScenarioName As String
Dim intResult As Integer
strScenarioName = Trim(scenariosDataGrid.DataKeys(e.Item.ItemIndex))
strMachineName = CType(e.Item.FindControl("txtMachine"), TextBox).Text
cmd = New SqlCommand("UPDATE siu_log SET Machines=@Machine " & _
"WHERE Scenarios=@Scenario", conn)
cmd.Parameters.Add("@Machine", strMachineName)
cmd.Parameters.Add("@Scenario", strScenarioName)
conn.Open()
intResult = cmd.ExecuteNonQuery()
resultLabel.Text = "The result was " & intResult & "."
conn.Close()
scenariosDataGrid.EditItemIndex = -1
BindData()
End Sub
The problem is the strMachineName variable always contains the previous contents of the text box -- not the new one. This makes the UPDATE query just push the old data back into the table.
Any suggestions?
Thanks!
John
View 5 Replies
ADVERTISEMENT
Mar 15, 2007
I have faced a situation that when i try to update a page. Some values can be updated while some cannot. I try to print the executed SQL query and get the following1 "UPDATE orders SET
2 cust_id=15,po_code='PO20060610',
3 po_amt=10000.0000,
4 add_charges=0,
5 commission='eeeeeee',
6 lab_charges=0,
7 fty_dis=0,
8 pay_trm='adasds',
9 cust_dis=0,
10 trade_trm_desc='',
11 curr_rate=1,
12 ship_expense=0,
13 shipmark='eng ship mard new2',
14 sidemark='Eng Side Mark new333'
15 ,inner_box='Eng Inner Box new333',
16 confirmation='rend confirmation2',
17 contract='end contact23',
18 internal_remark='testing testing 26/6/2007 333',
19 rec_curr_rate=0,rec_amt=0,shipmark_attach='',
20 sidemark_attach='',inner_box_attach='',
21 ord_type=1,status=2,ord_confirm_code='',
22 commission_type=1,sidemark_lang='English',
23 curr_code='HKD',unit_code='PCS',
24 trade_trm='FOB Hong Kong',rec_curr='USD',
25 ord_date='2006/06/10', po_date='01/01/2007',exp_delivery_date='01/01/2007',
26 act_delivery_date='01/01/2007', pay_start_date='10/10/06',pay_end_date='10/10/06',upd_time='2007/03/15 15:41:14' WHERE ord_id=292;Set @ord_id=292;"
The fields sidemark, inner_box, internal_remark cannot update, while others can.
I think it's really strange.... since i have no idea why some can be updated while some others and the SQL seems to me is correct.
Please give me some advices on solving this.
Thank you.
View 4 Replies
View Related
Nov 8, 2013
I have a grid like this and when I change Designation value of Name X from PA to PAT, this change should reflect to Sathish Designation too. Changing similar values should reflect all over the grid. Like update database on change of value of similar kind.
By far I use this query to update the grid. So a where clause should be used now to update the grid to fit this scenario.
("UPDATE AppInvent_Test SET Name = @Name, Designation = @Designation, City = @City WHERE EmpID = @EmpID");
EmpID Name Designation City
21 X PA Chn
2 Sathish PA Chn
3 Shiva A Cbe
17 Venkat M Hyd
22 Y SM Cbe
18 Vignesh SA Hyd
Table:
CREATE TABLE [dbo].[AppInvent_Test](
[EmpID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Designation] [varchar](100) NULL,
[Code] ....
View 4 Replies
View Related
Nov 18, 2013
I have a grid with checkbox, where users can select multiple rows and edit at the same time and save it to the DB. Now I have used a Footer Template with textbox in the gridview. So if I want to put similar data's for some particular rows at the same time in the grid, I select the multiple rows and try to put values in the footer template textbox and when I click on save, it saves successfully.
UPDATE QUERY:
"UPDATE [Test] SET [Name]='" + Name + "',[Designation]= '" + Designation + "', [City]= '" + City + "' WHERE EmpID='" + EmpID + "'";
Now here is the challenge, but even when I enter null values in the footer template textbox it has to save with the old values of the rows and not null values. I tried it and couldn't make it happen. So anything like putting the case for each column and mentioning like if null accept the old value and not null accept new value.
View 5 Replies
View Related
Jul 20, 2005
Help, please. I am trying to update atable with this structre:CREATE TABLE Queue (PropID int, EffDate smalldatetime,TxnAmt int)INSERT Queue (PropID) SELECT 1INSERT Queue (PropID) SELECT 2INSERT Queue (PropID) SELECT 3....from this table...CREATE TABLE Txns (PropID int, TxnDate smalldatetime,TxnType char(1), TxnAmt int)INSERT Txns SELECT 1 '20000201', 'B', 100000INSERT Txns SELECT 1 '20020515', 'B', 110000INSERT Txns SELECT 1 '20020515', 'A', 120000INSERT Txns SELECT 1 '20020615', 'c', 130000....only certain txn types are okay, and they have an orderof preference...CREATE TABLE GoodTxnTypes (GoodTxnType char(1), Pref)INSERT GoodTxnTypes SELECT 'A', 1INSERT GoodTxnTypes SELECT 'B', 2The idea is to fill in the NULL fields in the Queue table,according to a rule -- the transaction must be the latesttransaction within a date window, it must be one of the goodtxn types, and if there are two txns on that date, choosethe txn by the preferred txn type (A is preferred over B,according to the field Pref).If the time window were 20020101 to 20030101, the txnselected to update the Queue table would be this one:INSERT Txns SELECT 1 '20020515', 'A', 120000 -- there aretwo in the time window that are type A or B; they areboth on the same day, so the 'A' is preferred.If the time window were 20000101 to 20010101, this wouldbe selected because it is the only A or B type txn inthe interval:INSERT Txns SELECT 1 '20000201', 'B', 100000I'm looking for a statement that starts...UPDATE Queue SET EffDate = ...., TxnAmt = .... (EffDate,in this table, is the same as TxnDate in the Txn table).Assume we have @FirstDate and @LastDate available.Help, please. I'm getting stuck with (a) a sub-query tofind the relevant Txn records, and (b) another sub-querywithin that to find the MAX(TxnDate) within the timewindow. Filtering the Txn records on the basis of theGoodTxnTypes table is easy, as is ordering what is returned.But I'm having trouble joining the sub-queries back to theQueue table on the basis of PropId.
View 1 Replies
View Related
Nov 25, 2014
Look at the following code,
Create table #test
(
id int primary key,
Name varchar(100)
)
insert into #test values (1,'John')
insert into #test values (2,'Walker')
[Code] ....
-- Query 1 :
update #test set name = 'Joney' where id = 1
-- Query 2 :
set rowcount 1
update #test set name = 'Joney' where id = 1
set rowcount 0
1. #test table have primary key & clustered index.
2. Obviously only one row will be available for an id.
3. In query 1, will the sql server look for matching rows even after it found 1 row?
4. Will query 2 really gains some performance?
View 5 Replies
View Related
Feb 16, 2006
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
View 8 Replies
View Related
Jul 31, 2015
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
View 5 Replies
View Related
Oct 1, 2015
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[code]....
View 4 Replies
View Related
Jul 25, 2015
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
View 2 Replies
View Related
Oct 26, 2015
This is the database structure I setup:
--Main Table
CREATE TABLE [dbo].[tbl_RF_Items](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[ProgramID] [tinyint] NOT NULL,
[StatusID] [tinyint] NOT NULL,
[Item] [nvarchar](256) NOT NULL,
)
--Mapping Table
CREATE TABLE [dbo].[tbl_RF_Tags_Map_Items](
[TagID] [int] NOT NULL,
[ItemID] [int] NOT NULL,
[DateModified] [smalldatetime] NULL,
CONSTRAINT [PK_tbl_RF_Tags_Item_Map] PRIMARY KEY CLUSTERED
[Code] ....
This a result set of the Items Map table so far:
TagIDItemID
1284838
1291475
1291480
8284838
8291475
8291480
10284838
10291480
62291475
Each item will have 3 tags. I am having trouble on how to filter the data. For example if i chose TagID 1, 8, and 62, the result set should return only one result. If I do an IN clause, it acts like an OR and I need something to act like an AND.It seems like the only option is to do a dynamic where clause, but there are thousands of items and that might hinder performance of the database. Is there any other option?
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
Nov 22, 2013
I have two tables I am working with, they are "Institutions" and "InstitutionOversights". The relationship is one-to-many.
The sample data is below.
Table one:
InstitutionID, InstName
------------------------
1 School Alpha
2 School Beta
3 School Charlie
4 School Delta
Table two:
InstitutionOversightID, InstitutionID, Type
------------------------------------------------
1 1 Accreditation
2 1 Verifcation
3 1 Old System
I would like a query to return the results in the following format:
InstitutionID, InstName, TypeList
-----------------------------------------------
1 School Alpha Accreditation, Verification, Old System
2 School Beta null
3 School Charlie null
4 School Delta null
View 3 Replies
View Related
Jul 16, 2015
I have a nested query that retrieves a min value. I would like to know how do i retrieve the corresponding time with that.
my data looks like this
valuevaluetime
1212/31/14 14:51
12.42/19/15 23:30
[highlight=#ffff11]10.92/21/15 6:40[/highlight]
13.21/25/15 20:47
My min value is 10.9 and i need the date 02/21/15
my nested query is as follows
( select min(cast(f.valuestr as float))
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time)
the above returns me the 10.9
i modified the query
select min(cast(f.valuestr as float)),
f.valuetime
from bvfindings f
where f.ObjectName = 'Hb'
but i get all values then.
View 6 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 20, 2005
In VBA, I constructed the following to update all records intblmyTable with each records in tblmyTableTEMP having the sameUniqueID:UPDATEtblMyTable RIGHT JOIN tblMyTableTEMP ON tblMyTable.UniqueID =tblMyTableTEMP.UniqueIDSETtblMyTable.myField = tblMyTableTEMP.myField,tblMyTable.myField2 = tblMyTableTEMP.myField2,tblMyTable.myField3 = tblMyTableTEMP.myField3How is this done in a SQL Server Stored Procedure?Any help is appreciated.lq
View 8 Replies
View Related
Dec 12, 2014
I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.
SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME)
,d.fmc_prodlnk, d. fmc_cost
INTO #TestTable
FROM (
SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL
[Code] ....
Expected outcome
fmc_terminalfmc_prodlnkfmc_cost StartDateEndDate
1A2.25 NULL2014-12-03 00:04:00.000
1A2.26 2014-12-03 00:04:00.0002014-12-03 11:33:00.000
And so on….
View 4 Replies
View Related
Aug 6, 2015
I have below script
CREATE TABLE dbo.TestPivot(
CollectionDate DATETIME,
Products VARCHAR(40),
ItemCount INT
)
INSERT INTO dbo.TestPivot
SELECT '4/1/2015','Benz' , 20
[Code] ....
-- Original Output
ProductsApr 2015May 2015Jun 2015
Benz10-800NULL
Toyota5NULL-180
****Required output where ever we have negative values we need to display message Invalid out put message for those negative rows
ProductsApr 2015May 2015 Jun 2015
Benz10Invalid NULL
Toyota5NULL Invalid
View 2 Replies
View Related
Oct 10, 2005
Hello all. I have 2 tables members1 and members2.
members1 have a field called directory_services_idmembers2 also has a field directory_services_id and another one called employee_id
I need to update directory_services_id in members1 to the value employee_id in members2 Where members1.directory_services_id = members2.directory_services_id I dont want to update all the records. Only those records in members1 that have a match on directory_services_id in members2. So if there are 100 records that match on directory_services_id then i want to update only those 100 and not all the records.This is the query that I have so far.Update members1 M1 Set directory_services_id = (Select member_custom20 From members2 M2 Where M1.directory_services_id = M2.directory_services_id)Where M1.directory_services_id IN (Select directory_services_id From M2)And the error I am getting isServer: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'M1'.Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'Where'.Please help. Thank you.
View 2 Replies
View Related
Sep 15, 2006
I have two tables in an inner join. I'm detailing the tables with some of their fields below. These tables are in a database I'm creating to manage backup tapes. Most importantly, this database will inform me when backup tapes which have already been used can be recycled (e.g. after all the jobs on the tape are over 28 days old). I want to write something which will look at each tape in turn and, if all related backup jobs on that tape are aged, the tape status will be changed from Active to Spare.
Tapes
--TapeNo
--Status (Spare / Assigned)
Jobs
--JobNo
--Name
--Description
--TapeNo
--AgedJob (BIT field indicating whether or not the job has aged)
Each tape can have 0, 1 or many jobs and each job can be on more than one tape.
Anyway, I have the tables and relationsips set up and they're ok. Again, what I'm struggling with is how I take each tape and look at all its jobs and, if all have aged, change the Status for the tape to Spare. I'm using SQL Server 2000 (Access 2003 as front end) and am pretty new to SQL. I was thinking this could be done by using some kind of update query and subquery, but I'm stumped. Could someone please help ?
--
Paul Anderson
View 3 Replies
View Related
Jul 23, 2005
Can someome please advise what the equivalent query would be inMicrosoft SQL Server ... I've tried a number of combinations with nosuccess ... Thanks, Ralph Noble (ralph_noble@hotmail.com)================UPDATE INVENTORYINNER JOIN SALES ON (INVENTORY.BAR_CODE = SALES.BAR_CODE)AND (INVENTORY.PRODUCT_NBR = SALES.PRODUCT_NBR)SET INVENTORY.DATE_PURCHASED = "20050127"WHERE (((SALES.SOLD)="20050127"));
View 1 Replies
View Related
Apr 25, 2008
Membertable1 has SSN, Address1, Address2 as columns.
Membertable2 has SSN, Name, Address1, Address2 as columns.
For each SSN in Membertable2 I want to update the Address1 and Address2 from Membertable1 table Address columns if it is available.
Since it is SQL Server 2005, thinking of any 2005 features. SQL Statements only.
Thank you,
Gish
View 8 Replies
View Related
Dec 12, 2013
I have my sql tables and query as shown below :
CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);
[code]....
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year],
T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
Something like this :
+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----
View 3 Replies
View Related
Aug 10, 2014
Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
[code].....
View 1 Replies
View Related
Jan 14, 2005
Hello all,
Thought I would post here in case anybody can give some information.
Here is the background information:
I have 2 tables (stores and sales) from the Pubs database in Sql Server 2000 copied down to a SQL Server CE database. There is no foreign key/primary key relationship between the 2 tables in the CE database.
Here are the update queries that cause the error:
UPDATE st
SET st.zip = 66668
FROM stores st
INNER JOIN sales sa ON st.stor_id = sa.stor_id
AND st.stor_id = 6380
Update stores SET stores.zip = 55555
FROM sales, stores
WHERE stores.stor_id = 6380
AND stores.stor_id = sales.stor_id
Here is the error message that is generated when I run the query (Param 0 and Param 1 change according to what column and line the FROM clause is in):
Error: 0x80040e14 DB_E_ERRORSINCOMMAND
Native Error: (25501)
Description: There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]
Interface defining error: IID_ICommand
Param. 0: 2
Param. 1: 1
Param. 2: 0
Param. 3: FROM
Param. 4:
Param. 5:
I ran the 2 queries in SQL Query Analyzer in SQL Server 2000 and they worked just fine. I also created 2 new tables (stores1 and sales1) in SQL Server 2000 using the Select Into clause. The new tables were created from the sales table and stores table in the Pubs database. The new tables had no foreign key/primary key relationship.
I ran the queries again in Query Analyzer against the new tables and the queries produced no errors.
Any suggestions?
Thank you,
Aaron B
View 1 Replies
View Related
Feb 14, 2014
I am trying to Write an update string for individual partID's. I wrote this query below but it isn't populating the time to test.
SELECT 'UPDATE Parts SET = [TimeToTest]' + ' ' +
Convert(varchar, (select test From [dbo].[db_PartsCats] as c Join Parts As P on P.category = C.CatID Where PartID = 48727))
+ ' ' + 'WHERE PartID = ' + CONVERT(varchar, P.PartID)
From Parts As P
Where FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187
Order By categoryMy results:
Should get UPDATE Parts SET = [TimeToTest] 0.5000 WHERE PartID = 48871 But getting Nulls instead
View 5 Replies
View Related
Mar 11, 2015
I have run into a perplexing issue with how to UPDATE some rows in my table correctly.I have a Appointment table which has Appointment Times and a Appointment Names. However the Name is only showing on the Appt start Time line. I need it to show for its duration. So for example in my DDL Morning Appt only shows on at 8:00 I need it to show one each line until the next Appt Starts and so on. In this case Morning Appt should show at 8:00,8:15, 8:30.
CREATE TABLE #TEST ( ApptTime TIME, ApptName VARCHAR (20), DURATION TINYINT)
INSERT INTO #TEST VALUES ('8:00', 'Morning Appt', 45), ('8:15', NULL, NULL),('8:30', NULL,NULL),('8:45', 'Brunch Appt', 45),('9:00', NULL,NULL),('9:15', NULL,NULL),
('9:30', 'Afternoon Appt', 30),('9:45', NULL,NULL),('10:00', NULL,NULL).
View 3 Replies
View Related
Jul 23, 2005
Im using an ADP to connect to a SQL Sqever DB.In access it was really easy to sayInner join on table1 and table2 and update columnA from table1 withcolumnC from table2 where table1.key = table2.key and table2 columnB =1 and table2 columnD = 4I have tried all manner of beasts to get this thing to work..UPDATE dbo.GIS_EVENTS_TEMPSET FSTHARM1 =(SELECT HARMFULEVENTFROM HARMFULEVENTWHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))This almost works but ignors the 'HARMFULEVENT.UNITID = 1 ANDHARMFULEVENT.LISTORDER = 0' part which is really importantAny Help would be great....
View 4 Replies
View Related
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
Apr 3, 2014
I want to update one table with the value from variable using a set based approach.
so the line
>> select @List = coalesce(@list + ',','') + cast(id as varchar(10)) + cast(feetype as varchar(25))
works fine but I want to take @list and use it to update my customers table. here is the full code.
create table customers
(custid int, fee_history varchar(max))
insert into customers
(custid
, fee_history
[code]....
View 5 Replies
View Related
Dec 17, 2007
Hello,
I am converting old MS Access queries to T-SQL and ran into a problem. The results of the same update queries returned different results. The idea is to subtract each of the amounts of Table2 from Table1:
Source sample tables and content:
Table1
ID Amount
1 100
Table2
ID Amount
1 10
1 20
1 30
In Access (Orginal source):
UPDATE Table1
INNER JOIN Table2
ON Table1.ID = Table2.ID
SET Table1.Amount = Table1.Amount - Table2.Amount
In T-SQL (Converted):
UPDATE Table1
SET Table1.Amount = Table1.Amount - Table2.Amount
FROM Table1 INNER JOIN Table2
ON Table1.ID = Table2.ID
Syntax for T-SQL is different from Access. When both queries are ran on their respective database, Table1.Amount in access became 40 (100 - 10 - 20 - 30), but Table1.Amount in SQL became 90 (100 - 10).
It looks as if in T-SQL it only ran one row? Or it could be that in T-SQL, updates written to the database in batches, hence why Table1.Amount was not updated for all update instances? Any help would be greatly appreciated.
View 3 Replies
View Related
Jul 27, 2015
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)
But in result i want the table as -
Month Value
Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull
View 9 Replies
View Related
Nov 15, 2006
instead of insert into command
sqlcom.CommandText = "insert into Approve_Overtime(syainNo,ninka_nen_from,ninka_gatsu_from)values(@syainNo,@ninka_nen_from,@ninka_gatsu_from)
how can i change it using update command?
thnx
View 137 Replies
View Related
