SQL Update Query Help
Mar 23, 2004
I have 2 tables A and B. Due_Date in table B has to be updated by the max(Due_Date) from table A only if a record entry for the same ID is present in Table A, otherwise the date remains the same.
Table A
IDDue_Date
12001-01-01
22001-02-01
32002-03-04
32002-09-09
32004-01-01
52003-01-01
52004-03-03
Table B
IDDue_Date
12001-01-01
2
32003-02-02
42001-01-01
52004-01-01
62001-01-01
Table B (after Update)
IDDue_Date
12001-01-01(gets updated from table A)
22001-02-01(gets updated from Table A)
32004-01-01(gets updated from Table A)
42001-01-01(since no record in table A, date remains the same)
52004-03-03(gets updated from Table A)
62001-01-01(since no record in table A, date remains the same)
I need to write a stored procedure for this, input to this stored procedure will be the 'ID'. Please help me out.
Thanks in advance
-Rohit
View 3 Replies
ADVERTISEMENT
Mar 26, 2007
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';
View 2 Replies
View Related
Sep 15, 2001
I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this:
KeyField
Value1
Value2
Value3
The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
View 1 Replies
View Related
Jul 20, 2005
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Mar 1, 2007
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
View 2 Replies
View Related
Mar 30, 2006
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
View 2 Replies
View Related
Nov 19, 2004
I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on
the query thats causing teh problem is
--//////////////////////////////////// this is the one thats running
Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
GO
Update Provider_APC_2004_05
set EmAdmsCount12mths =
(Select COUNT(*)-1
from Combined_Admissions
where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
(Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
(Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
Combined_Admissions.AdmMethod like 'Emergency%')-- and
-- CA.NHSorPrivate = 'NHS'))
FROM Provider_APC_2004_05, Combined_Admissions
any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one
Dave
View 6 Replies
View Related
Jul 10, 2006
This is my first website done in ASP.NET and SQL Server 2005. I haven't ran into any major problems, until I tried to run an update on some rows. I've searched everywhere online, only to be stuck at the end of the day. I will post the code I have for the button that triggers it. I'll be crossing my fingers! int eid = Int32.Parse(Request.QueryString["id"]);
string updateSQL = "UPDATE Events SET event_title = @event_title, event_date = @event_date, ";
updateSQL += "event_time = @event_time, event_location = @event_location, event_description = @event_description WHERE (eid = @eid)";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);
cmd.CommandType = CommandType.Text;
// Add the parameters.
cmd.Parameters.AddWithValue("@event_title", txtEditTitle.Text);
cmd.Parameters.AddWithValue("@event_date", txtEditDate.Text);
cmd.Parameters.AddWithValue("@event_time", txtEditTime.Text);
cmd.Parameters.AddWithValue("@event_location", txtEditLocation.Text);
cmd.Parameters.AddWithValue("@event_description", txtEditDescription.Text);
cmd.Parameters.AddWithValue("@eid", eid);
// Try to open database and execute the update.
try
{
con.Open();
int updated = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Response.Write(err.Message);
}
finally
{
con.Close();
Response.Redirect("./?msg=eus");
}
View 11 Replies
View Related
Feb 19, 2007
Hi everyone!I have a update query which all looks good and it looks like it executes, though it does not effect the database for some unknown reason. (No error messages).protected void Button1_Click(object sender, EventArgs e)
{
int areaId = 0;
if (Request.QueryString["doc_area_id"] != null)
{
areaId = Convert.ToInt32(Request.QueryString["doc_area_id"]);
}
SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CPS_docshareConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("UPDATE document_area SET doc_area_name = '" + AreaText.Text + "' WHERE doc_area_id = @areaId", myConnection);
command.Parameters.Add(new SqlParameter("@areaId", areaId));
myConnection.Open();
command.ExecuteNonQuery();
myConnection.Close();
} Thanks for any help you can give, cheers, Mark.
View 8 Replies
View Related
Jul 19, 2007
I am using the update query listed below in my DetailsView. The issue i am having is the ManagerDecision value which gets populated in a DropDownList in EditTemplate, doesnt seem to update correctly. If I choose 'Approved' from the DropDownList, the result for Status_Type ends up being 'Closed' and not 'Open'. Could someone please Help.
Thanks!!
UPDATE ServiceRequest_Table SET ManagerDecision = @ManagerDecision, Comments = @Comments, Priority_Name = @Priority_Name, Status_Type = CASE WHEN (ManagerDecision = 'Approved') THEN 'Open' ELSE 'Closed' END, ManagerDateTime = CASE WHEN (Status_Type = 'Closed') THEN '' END, Closed_Date = CASE WHEN (Status_Type = 'Open') THEN '' END WHERE (ServiceRequest_ID = @ServiceRequest_ID)
View 33 Replies
View Related
Jul 23, 2007
OK, so I have this page where a user can edit multiple fields in a database record. My problem is, that once changes have been made and the user hits the "save" button, everything SEEMS to run just fine (no errors, other things that should happen when the button is clicked happen normally), but nothing is changed. It's almost like the update function (which was created with Microsoft ASP.NET Web Matrix) is working, but it's being fed the old values for the page, rather than the updated ones.
Any ideas on what might be causing this? It's not like anything I've seen before.
Thanks
Kaiti
View 13 Replies
View Related
Feb 16, 2008
I gave a storedprocedure for gatting data into gridview,just go through that.
Select Ind.Industry_Name,Com.Company_Address from Pcra_Industry Ind join Pcra_Company Com on Ind.Ind_ID_PK=Com.Ind_ID_FK Here iam getting two fields from two different table depending on the Id.
So how to write a Query to change the fields in the different tables.
please Help me...
View 1 Replies
View Related
Feb 19, 2008
Im having a problem when using my update statement on button click. I want the field to be updated to be true whenever the button was clicked but it doesn't seem to like my current syntax. Can someone point me in the right direction please? I know its really simple but when you have just been looking at something for so long you can never see it!!! Thanks
SqlCommand myCommand = new SqlCommand("Update Notice SET Resolute = '" + Resolute + "', DateClosed= '" + DateClosed + "', Resolved = 'True' WHERE NoticeID = " + NoticeID + "", myConnection);
View 3 Replies
View Related
Apr 22, 2008
Hi Guys
I have a problem with UPDATE query. Here is my Select query.
SELECT d.EID as EID,d.EName as EName,d.EDOB as EDOB,d.EDesignation as EDesignation,d.ESal as ESal,EI .EPhone as EPhone,EI .EEnddate as EndDate FROM Data_EMP d Join Emp_Info EI on d.EID=EI.EIDWhere d.EID ='1001'
According to this query I have to update another query.This is my original Update Query.
"Update Data_EMP set EStatus=0 where EID='" & TempEID & "'"
Now i wrote update query like this
UPDATE d.EID as EID,d.EName as EName,d.EDOB as EDOB,d.EDesignation as EDesignation,d.ESal as ESal,EI .EPhone as EPhone,EI .EEnddate as EndDate FROM Data_EMP d Join Emp_Info EI on d.EID=EI.EIDSet set EStatus=0 where EID='" & TempEID & "'"
Shall i write Update query like this and Is there any issues in this update query can any one let me know?
Thanks
View 2 Replies
View Related
Mar 7, 2006
Once a month I will have to run a query to update 2 fields
in one table, from another table.
Say Table1 is my main table to update and Table2 is the
table that it will update from.
Table2 is just a temporary table I will import monthly just
to update Table1.
Both tables have 2 pk/fk (userID and leaveDate).
Table1 has many of the same userID, but all different dates.
Table2 has only one instance of each userID
Example:
Table1
userID leaveDate other1 other2
1 1/1/06 6 14
1 2/1/06 2 12
1 3/1/06 <NULL> <NULL>
2 1/1/06 43 21
2 2/1/06 15 34
2 3/1/06 <NULL> <NULL>
Table2
userID leaveDate other1 other2
1 3/1/06 35 18
2 3/1/06 12 19
So Table 2 should update Table1.other1 AND Table1.other2
where userID AND leaveDate are equal
View 2 Replies
View Related
Jul 10, 2001
customerid lastname firstname emailaddress
14556928 tyler NANCY abc@hotmail.com
14556927 tyler NANCY abc@hotmail.com
I am going to create a mastercustomerid column whose value will be the min(customerid) in the above scenario.
I need a query where I can achieve this
I used the query below to achieve the above results
SELECT a.customerid, lastname, firstname, emailaddress
FROM account.Customer a Join account.Email b
ON a.customerid = b.customerid
WHERE lastname in
(
SELECT lastname
FROM account.Customer c Join account.Email d
ON c.customerid = d.customerid
GROUP BY lastname, firstname, emailaddress
HAVING count(*) >1
AND firstname = a.firstname
AND emailaddress = b.emailaddress
)
ORDER BY lastname, firstname, emailaddress
I need to update 349862 rows with a mastercustomerid . Please help me.
View 4 Replies
View Related
May 25, 2000
I have an update query which updates a field if the contents of 3 other fields in the table matches the result of a Where clause. Is it possible to only update the first matched row in the table rather than all rows that match the Where clause. Forgive if this is simple but I am a newbie to SQL 7.
Regards
Andrew Wall
View 1 Replies
View Related
Oct 26, 1999
I have the following table:
AreaName Population RankNumber
======== ========== ==========
Annerley 200 NULL
Balmoral 50 NULL
Chermside 350 NULL
Is it possible the write an update query that, after ordering the table by Population, it sets RankNumber to 1 for the first row, sets RankNumber to 2 for the second row, and so on??
The resultant table would look like:
AreaName Population RankNumber
======== ========== ==========
Balmoral 50 1
Annerley 200 2
Chermside 350 3
View 3 Replies
View Related
Oct 13, 1999
like so many i am upsizing some databases from access to sql server. how can i get the following access query to work in sql server 7.0
UPDATE dbo_t_Assignments INNER JOIN t_Assignments ON dbo_t_Assignments.AssignID = t_Assignments.AssignID SET dbo_t_Assignments.EmployeeID = [T_ASSIGNMENTS]![EMPLOYEEID], dbo_t_Assignments.DeptID = [T_ASSIGNMENTS]![DEPTID], dbo_t_Assignments.ShiftID = [T_ASSIGNMENTS]![SHIFTID], dbo_t_Assignments.JobClassID = [T_ASSIGNMENTS]![JOBCLASSID], dbo_t_Assignments.Regular = [T_ASSIGNMENTS]![REGULAR], dbo_t_Assignments.CreatedBy = [T_ASSIGNMENTS]![CREATEDBY], dbo_t_Assignments.TimeStampCreate = [T_ASSIGNMENTS]![TIMESTAMPCREATE], dbo_t_Assignments.UpdatedBy = [T_ASSIGNMENTS]![UPDATEDBY], dbo_t_Assignments.TimeStampUpdate = [T_ASSIGNMENTS]![TIMESTAMPUPDATE], dbo_t_Assignments.DateStart = [T_ASSIGNMENTS]![DATESTART], dbo_t_Assignments.DateEnd = [T_ASSIGNMENTS]![DATEEND], dbo_t_Assignments.rEmployeeID = [T_ASSIGNMENTS]![REMPLOYEEID];
thanks
Tony
View 1 Replies
View Related
Mar 15, 2001
I have a table with the following data.
CATEGORY ACCT CODE
C ABC
C XYZ
A EFG
A PQR
A LMN
B STU
D IJK
I want to update the code so that all records with the same category has the same code. It would also start with 1.
The end result would be the following:
CATEGORY ACCT CODE
C ABC 1
C XYZ 1
A EFG 2
A PQR 2
A LMN 2
B STU 3
D IJK 4
View 1 Replies
View Related
Apr 18, 2007
Hi,
I have got the following query that i want to concatenate to fields separate by a ; Can someone point out what is wrong please.
Thanks
Code:
Update wce_contact set Alt_Contact_Email = EmailAddress &';'& Alt_Email where Alt_Email <> ''
View 1 Replies
View Related
Feb 9, 2005
Maybe some one can help me...
I have 2 tables with the following structure:
CREATE TABLE [table1](
[GRID] [int] IDENTITY (1, 1) NOT NULL ,
[GID] [varchar] (10) NOT NULL ,
[RID] [int] NOT NULL ) On primary
CREATE TABLE [table2] (
[RID] [int] IDENTITY (1, 1) NOT NULL ,
[RText] [varchar] (400) NULL
) ON [PRIMARY]
If [RID] in table 1 exists more then one time (the first occurence leave as is)
and then create a new [RID] every time it is found in table2 with the RText from [RID] and update table1 with
applicable [RID] .
View 3 Replies
View Related
Jan 18, 2007
I have a query i need to write that will effect over 12,000 rows of data so i can't get it wrong! Can anyone give me some advice i can update individual records using ASP and uniqueid's but this query need to be done on SQL Exp browser.
What i have is 2 columns one called fxactivitystatus and one called activitystatus i need to copy all the fxactivitystatus fields to the activitystatus fields where the activitystatus are blank.
I would appreciate any help with this thanks.
View 8 Replies
View Related
May 19, 2008
I have a couple fields that I would like to run and Update Query on in SQL:
The Fields are called: Sample data
tp_Author | tp_Editor
785 785
785 785
758 785
What I am trying to do is update the two fields and each row of data with specific data, for example I would like the first
tp_Author
785 updated to 44
785 updated to 21
I want to update all the rows in both colums(fields) at the same time from the current data to the new data.
Will you method do the same for Dates?
PLEASESSSSSSSSSSSSS HELP Me:
View 7 Replies
View Related
Aug 11, 2004
HI Guys Wonderful to see you again and of course I have a question for ya.. I have a query that finds matching records in two tables one is the Conditional Table the other is the Termination table. What I would like to do is update the records it finds, which is basically a yes/no field. It bascially says if a terminated employee was once on a conditional they the field will say yes. and I need to update that field to yes. This query produces approximately 150 records that the Termination.tbl and the ConditionalLicense_View have in common and I need to change those 150 records Yes/No to a Yes?? does that make sense??
SELECT ConditionalLicense_View.[TM #], ConditionalLicense_View.FirstName, ConditionalLicense_View.LastName
FROM ConditionalLicense_View LEFT JOIN TERMINATION ON ConditionalLicense_View.[SS#] = TERMINATION.SocialSecurityNumber
WHERE (((TERMINATION.SocialSecurityNumber) Is not Null));
View 2 Replies
View Related
Oct 12, 2005
table1
(directoryid, firstname, lastname, extension, phonenumber)
table2
(directoryid, firstname, lastname, extension, phonenumber)
table2 is the latest table with updates, some of the fields in the extension field are nulls. i want to update table1.extension to table2.phonenumber based on the primary key directoryid
update table1
set table1.extension = table2.phonenumber
INNER JOIN table2
on
table1.directoryid = table2.directoryid
where table1.extension = '' or table1.extension is NULL.
Is my query correct ?
View 2 Replies
View Related
Nov 23, 2005
Hi There ,
I run the query below and get the error message below the query.
How can i do this update?
UPDATE GEO_Plaats
SET NetNummer = (SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats)
WHERE EXISTS
(SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);
ERROR:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Cheers Wimmo
View 3 Replies
View Related
Oct 26, 2006
I want to create a query where the system calculates the bonus for each transaction by looking up the SALES_STOCK_BONUS table to see if there is a valid bonus for the product in that transaction (when transaction falls between dateTo and dateFrom of a bonus)
(productHeadings is a link table between the SALES_STOCK table and SALES_STOCK_BONUS)
eg if heading.id = 25
and had a bonus of 13 between 01/01/04 and 01/04/05
and then had bonus 15 between 02/04/05 - 01/10/06
...but this query assigns the same bonus(13) for all transactions with product heading.id = 25 whateva the date....
UPDATE SALES
SET SALES.Bonus = SALES.Sale *(SALES_STOCK_BONUS.Bonus/100)
FROM SALES, SALES_STOCK, SALES_STOCK_BONUS, productHeadings
WHERE SALES.Product = SALES_STOCK.Product AND SALES.Whse = SALES_STOCK.Whse
and productHeadings.heading_name = SALES_STOCK.heading2
and SALES_STOCK_BONUS.id = productHeadings.id and
(CONVERT(CHAR(10),SALES.[Date],110)>= CONVERT(CHAR(10),DateFrom,110)
and CONVERT(CHAR(10),SALES.[Date],110) <= CONVERT(CHAR(10),DateTo,110))
and SALES_STOCK_BONUS.Bonus is not null
It doesnt seem to check the date for each one individually.... PLS help!!!
View 1 Replies
View Related
Nov 28, 2006
The query below executes successfully but it updates all the records. I want to update only the parameters on the 'where' clause. What am I missing?
Thanks.
UPDATE tblIncident
SET UnprocessedPoints = 6
FROM [tblIncident] as Incident, [tblperformedaudit] as Audit
where ((incident.databaseid = audit.databaseid)
and (audit.auditnbr = 2)
AND (audit.doctype = 'CLAIM')OR(audit.doctype = 'TAR') OR (audit.doctype = 'CIF')
AND (audit.monthofsample = 'oct')
And (Incident.Status <> 'Removed' )
AND (Incident.typeofFindings = 'Error')
AND (Incident.errorID = 'Unprocessed'))
View 5 Replies
View Related
Jan 16, 2007
hello,
This is the update query that I have created.
Let's say @UnderlyingIndexID is to be set to null instead of an existing value of 10.
I think this query does not allow for such an update.
How can this query be altered to do so?
Thanks
update
tblTest
set
IsActive = isnull(@IsActive, IsActive),
UnderlyingIndexID = isnull(@UnderlyingIndexID, UnderlyingIndexID),
LastUpdatedUser = @ActiveUser,
LastUpdatedDate = getdate()
where
IndexID = @IndexID
View 3 Replies
View Related
Feb 20, 2004
Folks
I m trying to load data from the same table in one database to the second database.
The data in the first database branch table is
ex,<null>,<null>,<null> in the three fields.
sCommand_DiscRevDev.CommandText = "UPDATE Branch" & _
" set Branch_Nm = '" & RTrim(Replace(drCode("Branch_Nm"), "'", "''")) & "'," & _
" Addr_line_1_Tx = '" & drCode("Addr_line_1_Tx") & "', " & _
" Addr_line_2_Tx = '" & drCode("Addr_line_2_Tx") & "'
" where Branch_Cd = " & drCode("Branch_Cd") & " "
The result set is
update branch set branch_nm='ex',addr1='',addr2=''
where branch_cd = 1
The data is load as a an emptry string ie the word "NULL"
is not appearing the query.
Can you suggest what function do I need to use to get the word NULL in the update
query.
Thanks
View 1 Replies
View Related
Feb 26, 2004
I have two tables, returnparts and dataimport. I am trying to update the ReviewNumber in the dataimport table with the reviewnumber in the returnparts table. Here is my query...
UPDATE DataImport
SET dataimport.ReviewNumber = returnparts.ReviewNumber
From returnparts r, dataimport d
where d.Customer = r.Customer and
d.[Review Date] = r.ReviewDate and
d.[Ref #] = r.referencenumber and
d.disposition = r.Disposition and
d.[Vehicle ID Number] = r.VIN and
d.[Model Year] = r.ModelYear and
d.[repair order #] = r.repairordernumber and
d.[repair date] = r.repairorderdate and
d.engine = r.engine and
d.model = r.model and
d.mileage = r.mileage and
d.[customer comments] = r.customercomments
Here is the error message I receive when I run this query.
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'returnparts' does not match with a table name or alias name used in the query.
I thought I had the syntax correct but apparently I don't. Any help would be appreciated.
View 10 Replies
View Related
Apr 11, 2008
I am trying to update a field based on two conditions.
UPDATE VoicewareSQL1.dbo.Dictations
SET state = 22
Where doctorID = 920 and state = 18
I get the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I tried making the conditons a subquery, but could not get that to work either.
Thanks in advance for assistance
View 20 Replies
View Related
