Complicated Update Statement
Jul 20, 2005
Hi All,
I need to write one complicated update statement and I'm looking at
maybe finding a simpler way to do it.
I have 2 tables:
1.Photo Table
PhotoID FileName
1 111.jpg
1 111_01.jpg
2 222.jpg
2 222_01.jpg
2 222_02.jpg
3 333.jpg
2.PhotoReport Table
PhotoID FileName1 FileName2 FileName3.....FileName12
1 111.jpg 111_01.jpg NULL NULL
2 222.jpg 222_01.jpg 222_02.jpg
..
..
..
I need to update PhotoReport Table to look like an example above. I've
started writing my code and it looks very hedeous with multiple nested
cursors.
So if someone has a sample of a code to accomplish this, please pretty
please send it my way. I appreciate it in advance.
Thank you,
Narine
View 3 Replies
ADVERTISEMENT
Nov 17, 2007
Hi!I have tables like:States: Club persons----------- --------- --------------*id *id *id*Name *Name *FirstName *StateID *LastName *ClubIDNow I want to select the name of all clubs in a particular state (given the state-id) and also the number of persons in that club, for example:California:--------------------------------San Francisco Bridge Club (4 members)Los Angeles Bridge Club (9 members) How can I realise this with MS-SQL 2000? :-) Thank you for help!
View 5 Replies
View Related
Mar 30, 2006
After hours of trying, I finally got a select statement to return what I needed, but I am not sure how I am doing is the most efficient way. Please give me your input.
Here's the situation: I want to allow customers to add to their magazine subscriptions online, so I created an aspx form that shows the magazines they are currently subscribed to and allows them to choose from other "available" magazines. In the available magazines field, I want to show all magazines that we have minus the ones the customer is already subscribed to.
MY pb table lists all of the magazines. The info table lists customers and their current magazine subscriptions.
I also have a table, PBExclusive that is for magazines that we have, but only want to be available to certain customers. So, I also need to make sure the "available" magazines field doesn't list any of the exclusions unless the customer is listed as the exception for that magazine.
Here is what I have:
("SELECT p.code, p.WebName FROM pb p WHERE (p.code IN(Select e.code FROM PBExclusive e WHERE e.id = " & lblID.Text & ") OR p.code NOT IN(Select e.code From PBExclusive e Where e.code = p.code)) AND p.code NOT IN (SELECT i.code FROM info i, pb p WHERE i.id = " & lblID.Text & " AND i.code = p.code)AND p.contract <> '1' AND p.code <> '00' AND WebListing <> '0' AND p.code <> '' AND p.code <> 'SU' AND p.code<> 'AGC' ORDER BY p.WebName", conn)
View 1 Replies
View Related
Mar 22, 1999
I have the following stored procedure that I use for an update. The table now has just over two million rows and the stored procedure takes days to run. I am looking for any help that would produce the same results faster. This runs on SQL Server 6.5, on an NT machine with 4 pentium pro 200 processors and 512 MB of RAM so I am relatively sure that the performance issue is in the below statement.
UPDATE PAY_CHECK_DETAILS
SET JobID = j.JobID
,HROrganizationID = j.HROrganizationID
,JobDetailID = j.JobDetailID
,GLAccountNumber = j.GLAccountNumber
FROM JOBS j, PAY_CHECK_DETAILS p
WHERE j.EmployeeID = p.EmployeeID
AND j.HRActionEffectiveDate =
(SELECT max(HRActionEffectiveDate)
FROM JOBS jj
WHERE p.EmployeeID = jj.EmployeeID
AND jj.HRActionEffectiveDate <= p.PayDate)
AND j.HRActionSequence =
(SELECT max(HRActionSequence)
FROM JOBS jjj
WHERE p.EmployeeID = jjj.EmployeeID
AND jjj.HRActionEffectiveDate =
(SELECT max(HRActionEffectiveDate)
FROM JOBS jjjj
WHERE p.EmployeeID = jjjj.EmployeeID
AND jjjj.HRActionEffectiveDate <= p.PayDate))
AND NOT (ISNULL(p.JobID,1) = ISNULL(j.JobID,1)
AND ISNULL(p.HROrganizationID,'A') = ISNULL(j.HROrganizationID,'A')
AND ISNULL(p.JobDetailID,1) = ISNULL(j.JobDetailID,1)
AND ISNULL(p.GLAccountNumber,'A') = ISNULL(j.GLAccountNumber,'A'))
Thanks for any help or suggestions you have.
Keith
View 3 Replies
View Related
Mar 4, 2008
I have the table tbOld with columns as fieldID (PK), Class, Name, other1, other2, other3 and tbNew with the same structure (fieldID (PK), Class, Name, other1, other2, other3).
Most Class-Name combinations are the same in both tables but have different FieldID, others are only in the 'tbNew' and absent in the tbOld, some are only in the 'tbOld' but we are not interested in those.
We need to update data in tbNew for columns other1, other2, other3 and make them the same as in tbOld where possible (where class-Name combination is present in the tbOld)
The update statement based on the join on two columns does not work (both columns are not PK, as FieldID is PK in each table, but we can't join on it)
Any help is highly appreciated.
View 2 Replies
View Related
Jan 3, 2008
Hello..
The first problem:
I have 2 tables in MS SQL database: topicstbl and txtfilestbl. And every one have column topicID.
And in both I have fulltext column I want to search in it; but in the first one I want to search in the fulltext column (topicflds) and also in normal columns (topicname and topicsn)
I need to search in these tables and get the (topicID) column as result by 1 table without repeated results.
I tried this code:
SELECT topicID, KEY_TBL.RANK FROM (SELECT topicID, 255 as RANK FROM topicstbl WHERE topicname like '%search word%' or topicsn like '%search word%') AS KEY_TBL
UNION
SELECT topicID, KEY_TBL.RANK FROM topicstbl AS topicstbl INNER JOIN CONTAINSTABLE(topicstbl, topicflds, 'search NEAR word') AS KEY_TBL ON topicstbl.topicID = KEY_TBL.[KEY]
UNION
SELECT topicID, KEY_TBL.RANK FROM txtfilestbl AS txtfilestbl INNER JOIN CONTAINSTABLE(txtfilestbl, txtfile, 'search NEAR word') AS KEY_TBL ON txtfilestbl.txtfileID = KEY_TBL.[KEY] ORDER BY KEY_TBL.RANK DESC
But the problem is the result table has repeated records (same topicID, different Rank Value).
Also I used "SELECT DISTINCT" but same problem.
The second problem:
I want to merge the previous code with ROW_NUMBER code to get results between x and y.
Like this code:
WITH OrderedOrders AS (SELECT *, ROW_NUMBER()
OVER (order by topicID DESC) as RowNumber
FROM topicstbl)
SELECT * FROM OrderedOrders
WHERE RowNumber between 1 and 50
But without change the first order.
Now how to do all these??!
View 6 Replies
View Related
Aug 25, 2005
My company works with a fairly large database that needs to be kept live.
My problem is that we need a method of updating the database without messing the data, or crashing the website.
I am sure this is a common problem with a number of solutions. Could
anyone please direct me to a good article on the best practices for
updating databases like this?
View 9 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 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
Aug 13, 2014
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
View 1 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Jul 23, 2005
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View 1 Replies
View Related
Nov 9, 2007
It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
View 5 Replies
View Related
Jul 30, 2007
Hello,
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
View 6 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
Jun 14, 2007
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
View 3 Replies
View Related
Apr 27, 2007
Perhaps you can help with something that I consider kind of omplicated? (I am SO hoping that I am NOT going to have to do this manually!!!)
I have a table (about 3000 rows) where two of the columns have Domain User information.
COL1 has DOMAINUsername and COL2 has (or SHOULD have) DOMAIN@username.com
I need to look at each field in COL1 and if exists DOMAINusername, I need to populate COL2 with username@DOMAIN.com
Is this possible???
View 8 Replies
View Related
Aug 27, 2007
The low down:
Local Web Server on Windows 2003
Local SQL Server on Windows 2003
Hosting dynamic website tied to inhouse Access Application
Ok, basically, how it is set up, people can login to our website and enter data (insert record), on our end, we have an Access application where we can play with the data that was entered via the website. Currently, we do not have either server set up as a Mail server.
What we need to be able to do:
When a customer enters data on our website, their supervisor, and about 2-3 other people related to the transaction need to be emailed to be notified that an order was submitted. So how do I code that? On the page with the Insert Record? OR after Insert Record redirect them to another page that sends the mail out?
Which server do I enable the mail?
I was reading about SQL Mail etc, which would be good since we do mass emails to clients weekly, but I have no idea how to set that up and I look crossed eyed at any tutorial.
Do I want to set up theSQL server to also be a Mail Server that way we can use the SQL database to email as well as data entered from the website? But then again, the website points to the Web Server which pulls data off the SQL server (so unless the Web Server is a mail server, nothing will be sent, am I right?)
*sigh*
I know very little about SQL and I'm being asked to impliment this and I am 100% confused. I'm a graphics artist not a programmer! LOL
Thanks in advance to anyone who can/will help me.
View 6 Replies
View Related
Aug 22, 2007
Dim lblock As Boolean chkChecked = lblock strSQL = "UPDATE CLIENTS SET " If blnCompleted = True Then strSQL = strSQL & "COMPLETED_DT = '" & Format(Now(), "MM/dd/yyyy") & "', " Else strSQL = strSQL & "LAST_SAVED_DT = '" & Format(Now(), "MM/dd/yyyy") & "', " End If strSQL = strSQL & "COMMENTS = '" & FixString(txtcomments.Text) & "' " _ & "WHERE client_ID = " & iclientID & ""I want to put my booleen value lblock to sql too, I probably need value of it, It is checkbox, called chkblock, . how would I include this to update statement database field for that BLOCK =
View 1 Replies
View Related
Sep 7, 2007
Hi, i nid help on update statement. I using 03 and a microsoft sql server 2000 database.
I use a more simple example of my error. A Northwind Database is use to update the Region table(RegionDescription)
User will 1st go in WebForm2.aspx and enter a id, if found will retrieve the data to WebForm1.aspx. User type "1" and retrieve Eastern to TextBox1.
User can choose to update the table by typing in a diff word into TextBox1. But when i type any word(e.g East) the page is refresh back to Webform1.aspx with the not updated data and the database is also not updated. Any idea?
WebForm2.aspx.vb Imports System.Data.SqlClient Public Class WebForm2
Inherits System.Web.UI.PageWeb Form Designer Generated Code Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page hereEnd SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Session("id") = TextBox1.Text
Response.Redirect("WebForm1.aspx")End Sub
End Class
WebForm1.aspx.vb Imports System.Data.SqlClient Public Class WebForm1
Inherits System.Web.UI.Page
Web Form Designer Generated CodeDim cnn As New SqlConnection("Data Source=(local); Initial Catalog=Northwind;User ID=******; Password=******") Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Label1.Text = Session("id")
retrieveTitle()End SubSub retrieveTitle()
cnn.Open()Dim cmd As New SqlCommand
cmd.CommandText = "SELECT * FROM Region WHERE RegionID = '" + Session("id") + "'"
cmd.Connection = cnnDim dr As SqlDataReader
dr = cmd.ExecuteReader()
If dr.Read() Then
TextBox1.Text = dr("RegionDescription").ToString
End If
cnn.Close()End SubSub UpdateTitle(ByVal title As String)
cnn.Open()Dim sqlstr As String = "UPDATE Region SET RegionDescription = '" + title + "' WHERE RegionID = '" + Session("id") + "'"
Trace.Write(sqlstr)Dim cmd As New SqlCommand(sqlstr, cnn)
cmd.ExecuteNonQuery()
cnn.Close()End SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
UpdateTitle(TextBox1.Text)End Sub
End Class
View 4 Replies
View Related
Mar 13, 2008
I have a SQL Table with the following columns
ID, Date, Meeting, Venue, Notes
What Update statement do i need to update a simple grid view in Visual Studio?I have been experementing but when i click update it updates the whole column insted of the one i was trying to update.
Please can you help? Thanks
View 1 Replies
View Related
Jun 4, 2008
I need some help. please. Here is what I got. From the webpage I can pull the following data to update a table. update Vehicle set Name='TestUnitName' ,Make='TestMake', Model='TestModel', SoftwareVersion='TestVersion', DynamicChange='1', ProviderID='1', Description='TestDescription', VIN='TestVIN', IMEI='TestSIM', EngineTypeId=' ', Phone=' ', MobilePhoneProviderID='' where VehicleID=64 But I also get the error: "The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Vehicle_EngineTypes". The conflict occurred in database "Telemetry", table "dbo.EngineTypes", column 'EngineTypeId'.The statement has been terminated." How do I solve this issue? Actually what I am trying to do is, when EngineTypeId=' ' , I want to set is to NULL and same for the MobilePhoneProviderID. Any help would be appreciated. Thanks in advance.
View 14 Replies
View Related
May 17, 2004
Hey all,
I need to set a column value where the id is within a string. However, I need to set the column to a default value if the id is not within the string. Hope that was easy to understand!
ie:
This currently works...
SET @strSQL = 'UPDATE tblTest SET Archive = 1 WHERE RecID IN (' + @IDList + ')
If the ID is not in the list then I want that column set to 0. How can I do this?
Thanks in advance,
Pete
View 5 Replies
View Related
Sep 2, 2004
Hi
I use a update sub, the problem is that i got an error, the error is:
Syntax error in UPDATE statement.
I guess the UPDATE statement is:
strUpdate = "Update tblUsers Set UserName=@UserName, Password=@Password, RetypePassword=@RetypePassword, Email=@Email, Comments=@Comments Where UserID=@UserID"
Remark:I use Acceess DataBase, exactly the same code works fine in SQL, i just changed the DataBase(From Access to SQL).
Is the problem can be in other place?
Thank you very much for your assistance.
View 3 Replies
View Related
Oct 14, 2004
Hi, I'm having trouble writing this update statement and was wondering if anyone could help me out :
My database is sort of set up lilke this:
There are 3 tables: Orders, OrderDetails and Inventory
Orders has a pk called OrderID.
OD has several ProductIDs listed for that OrderID
Inventory has 2 fields, the pk InventoryID(which is the same as
ProductID) and QOH
So OD kinda looks like this:
OrderID ProductID Quantity Cost
192 12 2 $10
192 3 1 $12
192 14 2 $50
193 12 1 $11
.... .
...
...
so what i want to do is take each productID for a specific orderid
and decrement the inventory for it by OD.quantity
This is what I was trying
UPDATE Inventory
Set QOH = QOH - @qty
WHERE Inventory.InventoryID IN (
SELECT ProductID
FROM OrderDetails
WHERE OrderDetails.OrderID = @OrderID
and @qty = OrderDetails.Quantity
)
but, i'm having no luck...... any suggestions ?
View 2 Replies
View Related
Dec 15, 2004
what would the syntax be for the following update statement?
"UPDATE [Stocklist]
SET
[client] to textbox2.text
[notes] to textbox3.text
[paid] to textbox4.text
[status] to "old"
WHERE
[Make] = dropdownlist1.SelectedValue
[Model] = dropdownlist2.SelectedValue
[IMEI] = dropdownlist3.SelectedValue
[status] = new"
I know this is a simple question even for someone who is just starting out , thats why I posted it into the "Getting Started" forum.
Thanx in Advance
View 2 Replies
View Related
Feb 15, 2001
Hi,
I am a SQL Novice - I have a table called 'users' in which I need to modify the user's email id e.g., from seanu@hotmail.com to seanu@yahoo.com and I have over 150 rows where I should be changing 'domain' name only..
Any help with an Update Statement is highly appreciated..
Thanks,
-Srini.
View 2 Replies
View Related
Oct 27, 2000
I have a table that looks like this.
tkinit adir adate1
0007 0.00 01/01/1996
0007 0.00 01/01/1997
0007 25.00 01/01/1998
0007 27.00 06/01/1998
0008 0.00 01/01/1996
0008 0.00 01/01/1997
0008 32.00 01/01/1998
0008 37.00 06/01/1998
I need to populate the adir field where the adate < 12/31/1998 with the rate where adate = 01/01/1998.
I cant seem to figure out exactly how the statement should go so that I populate each timekeepers rate with their specific rate.
Thanks
Jason Fitch
View 2 Replies
View Related
Sep 27, 1999
Is there any way to create a "dynamic" update statement in a stored procedure?
Lets say I want to choose whether to update all columns in a table or just one. If I want to update just one column, is there a way to make the other columns keeping their values instead of NULL?
Thanks!
Pete
View 1 Replies
View Related
Aug 29, 2003
I am looking for a solution to my unique problem.
myTable is having following records
ID Field1 Field2 Field3 Field4
1 x y 100 0
2 xx zz 5 0
3 x y 200 1
4 a b 1 1
5 a b 2 0
6 k L 78 1
7 aa bb 25 1
8 k L 15 0
Now the problem is if I want to update a latest record's(if you go by ID)Field4 with 2 for the records where Field1 + Filed2 is unique, can I do this in a single query?
I can select the unique record by the following query
select Field1,Field2 from myTable group by Field1,Field2 having count(ID)>1
Any ideas?
View 4 Replies
View Related
Mar 3, 2008
Hi first post, so apologies if this is in wrong forum.
I am needing to update a field in one table, where a particular value of another table is TRUE, and update that table based on a common field in both tables.
at the moment my query is:
IF EXISTS (SELECT * FROM _tblSnapShotAUSProfServicesOrderLines_Daily
WHERE _tblSnapShotAUSProfServicesOrderLines_Daily.Produc tRelProductName like 'Prof%')
UPDATE OrderHeader_details
SET ServicesSold = 'Y'
WHERE OrderHeader_details.OrderRef = _tblSnapShotAUSProfServicesOrderLines_Daily.OrderR ef
In its current format, I get the column prefix is incorrect which makes sense sort of.
No matter which way I change this, I cannot get the query to work. I one form, it works but essentially performs two seperate statements, one with the select, and one with the update which unfortunately updates every row in the table.
Any assistance is appreciated.
Mick
View 1 Replies
View Related
Sep 24, 2007
Hello,
I need help with a sql update statement i need it to convert a datetime value to a specific format. For some reason it statement will only update the row only when i convert the datetime value to a varchar, i ran it in mssql sql server management studio and it works, the database is a mssql 2000 i think. If i try to compare the datetime value and they are both DateTime values it fails, if i convert them to varchar and run them they succeed, what i also noticed is when when are date values are that the value i passed in the query is that they are in a different format, i guess the database stores them as mon/dd/yyyy hh:mm:ss. but when i run the query that failed i get this as the results of the values from variables @Date1 & @Date2: They are the same and they failed. So if anyone knows any thing any help is surely appreciated this really sucks.
Failed
Date 1: Sep 23 2007 9:54PM
Date 2: Sep 23 2007 9:54PM
---------------------------------------------
Need help with making this work in C#:
Code:
string sql = string.Format(
"UPDATE [Contact Sheet] SET CallAttempt1 = {0}, CallAttempt2 = {1}, "
+ "CallAttempt3 = {2}, Status = '{3}' WHERE ContactID = {4} AND ModifiedDate = 'CONVERT(VARCHAR(50), {5}, 0)'", ..............................);
This statement ran in MSSQl Studio Management and works:
Code:
DECLARE @Date1 AS VARCHAR(50)
DECLARE @Date2 AS VARCHAR(50)
SET @Date2 = CONVERT(DATETIME, '9/23/2007 9:54:40 PM', 0);
SET @Date1 =
(SELECT Convert(VARCHAR(50), ModifiedDate, 0) FROM [Contact Sheet] WHERE CustomerID = 22);
IF @DATE1 = @DATE2
BEGIN
PRINT 'Success'
END
ELSE
BEGIN
PRINT 'Failed'
-- PRINT @TmpDate
PRINT 'Date 1: ' + @DATE1
PRINT 'Date 2: ' + @Date2
END
This one fails everytime:
Code:
DECLARE @Date1 AS DATETIME
DECLARE @Date2 AS DATETIME
SET @Date2 = CONVERT(DATETIME, '9/23/2007 9:54:40 PM', 0);
SET @Date1 =
(SELECT Convert(DATETIME, ModifiedDate, 0) FROM [Contact Sheet] WHERE CustomerID = 22);
IF @DATE1 = @DATE2
BEGIN
PRINT 'Success'
END
ELSE
BEGIN
PRINT 'Failed'
-- PRINT @TmpDate
PRINT 'Date 1: ' + CONVERT(VARCHAR(50), @DATE1, 0)
PRINT 'Date 2: ' + CONVERT(VARCHAR(50), @Date2, 0)
END
View 1 Replies
View Related
May 19, 2005
can I do something like this in update statement
update test
set Code=
Case When Action='D' then 'Failed'end,
from test1
View 2 Replies
View Related
