Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View Complete Forum Thread with Replies
Related Forum Messages:
Trouble Converting SELECT To INSERT Statement
Trying to convert the following SELECT statement into a INSERT statement and having trouble. No doubt this will be a piece of cake to someone. To eventually get this to a trigger stage would be nice, but for the moment I'd settle for just plain SQL. Using MS SQL 2000. The database name is reporting. The table name is CallLog. I'm trying to convert seperate date (RecvdDate) and time (RecvdTime) columns into a single DateTime column. I've scoured a lot of web pages but I'm still lost. ============== use reporting go SELECT RecvdDate + RecvdTime FROM [dbo].[CallLog] =============== Any help much appreciated.
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
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 Replies !
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Using Select Statement Result In If Statement Please Help
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
View Replies !
If STATEMENT Within Select Statement Syntax
Hi, I am a newbie to this site and hope someone can help.... I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is: if(TL_flag= '1', "yes") as [Trial Leave] it is coming up with an error.... I can use Select case but I should not need to as this should work? Any ideas?
View Replies !
Converting Sql Statement To Oracle
Can anyone tell me how to write the following SQL code in Oracle? select convert(datetime,convert(varchar,datepart(month,da teadd(month, -1,getdate())))+'/01/'+convert(varchar,datepart(year,getdate()-1)))as Start_Month, convert(datetime,convert(varchar,datepart(month,ge tdate()))+'/01/'+convert(varchar,datepart(year,getdate()-1)))-1 as End_Month
View Replies !
Converting Data Like A Case Statement
Hello. I have data in a SSIS package that I need to alter to something else. The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS". The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'. I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword. Can I use a data conversion task to do this in SSIS? and if so, what's the syntax? Thanks
View Replies !
Using IF...Else Statement SELECT Statement
Hi All, Can some one point me in the right direction in how to construct my SQL query within my cursor? I Have got a cursor which i am using to iterate through a table, What i am trying to do is in my statement(used to open the cursor) is compare 2 tables (the one which my cursor is iterating) to see if there is a matching row in the other table (using both tables ID's Like So: SELECT column_List FROM Table1 WHERE Table1_id = Table2_id so for each row my cursor checks if there is a corresponding match in table2... but i would like to write to an error log and do other statements if there is no match how do i add this condition to my statement either using an if...else statement proceeding to the next row? here is the statment i attempted to write: SELECT column_List FROM table1 WHERE Table1_id = Table2.id now i want to incoporate the statements below into the statement above as a condition when table1.id <> table2.id IF table1.id <> table2.id BEGIN SET @DebugMessage = 'data not live.' RAISERROR (@DebugMessage, 16, 1) WITH LOG END essentially what i am trying to sayin my statement is: go to the first row check if it has a match in table 2, if there is no match execute a number of statements such as error loging e.t.c go to the next row repeat the previous statements ...i also looked through some Case...When statements am just not sure how to put in the condition thanks in advance
View Replies !
Help With DELETE Statement
Hello there... I'm creating a ASP.net Web Application and storing all my data in SQL database.I'm trying to create a Stored Procedure to Delete info from a table:- I have 3 tables: A, B and C: A's primary key is aID and has no foreign key B's primary key is bID and has 1 foreign key: aID (linking this table to table A); C's primary key is cID and has 1 foreign key: bID (linking this table to table B);- As you can see, all 3 tables are linked (A to B and B to C)I want to be able to DELETE all info from C only by giving aID, is this possible in SQL? I can retrieve the info easily through a SELECT statement and using an INNER JOIN (*). I also know how to do solve this by coding it in C# but I'd have to create some unnecessary variables and more than 1 Stored Procedure when it's probably possible to do all in one!* SELECT cID FROM C INNER JOIN B ON B.bID = C.bIDWHERE A.aID= whateverIf anyone knows the solution to my problem, please don't hesitate :p Thanks,SuperJB
View Replies !
Delete Statement
Hi,I would like to delete a record from a table on the condition that a corresponding ID is located in another table, ie. deleting an email message if the user ID is listed as a recipient in a recipient table etc. Here is my SQL statement: DELETE FROM id_email_message WHERE (id_message IN (SELECT id_message FROM recipients WHERE id_user = 324) AND message.id_message_status = 2) OR (id_message IN (SELECT id_message FROM message WHERE id_owner = 324 and id_message_status = 2)) The problem is the multiple select statements paired with the delete statement is too much overhead for the server and I always get a timeout server error (at least that's what I'm guessing, the error page and tracing isn't much helpful). Is there a more efficient way to do this?Thanks.Eitan
View Replies !
Sql Delete Statement
hi, i want to delete some records from my table if there is more then 150 records (it should always be max 150records, it can be less and then it shouldent delete anything), so when it goes over to 151 records i want to delete the oldest record, so i get the new record + 149 old records, is there a simple way to do this?
View Replies !
Delete Statement
I accedentally doubled the amount of records in my table using a DTS package. How can I remove the records ? I beleive they were appended to the table. What would be the syntax in the statement that I need to do this? Thanks, Mark
View Replies !
DELETE Statement
When does the DELETE statement physically deletes the records? For example, if I execute the DELETE statement and in the middle of the execution I understand that it is wrong. What will happen if I stop it? Will it delete the records partially? I think the deletion happens when the full statement is done but need an expert answer. Thank you.
View Replies !
SQL Delete Statement
Hi what do i need to add to this stmt to delete the result ? --------------- select ct_cust1_text01,ct_address,ct_cust1_text09,count(*)from TABLE_NAME group by ct_cust1_text01,ct_address,ct_cust1_text09 having count(*) > 1 --------- i have tried delete * from TABLE_NAME where (select...) not great at SQL appreciate any help...
View Replies !
Easy One(?) Converting Sql05 Statement To Work In Sql Express
Hi, I was handed an old application written for sql05, it has this (kind of) statement: Dim CommandText As String = "SELECT c.name FROM db1 c LEFT JOIN " CommandText += "db2.dbo.Users u ON u.iKey = c.key " CommandText += "WHERE u.cUserName = '" & User & "'" As you can see, there are two databases, db1 and db2 and this worked fine in sql05, but when I convert each of the databases to sqlexpress, I get this error: Invalid object name 'db2.dbo.Users'. So how can I make this work in sqlexpress? Are queries across databases not allowed? Thanks so much for your help!
View Replies !
Trigger On Delete Statement
Hi! I am deleting some records from the database and at the same time inserting them into a new table. I am doing all this with sql querries. How can that be done with triggers. Basically on the delete, i'd like to insert the affected records. Thanks! James
View Replies !
DELETE Statement Conflicted
Hello I am trying to delete a row from one table and I expected it to also be removed from the subsequent child tables, linked via foreign and primary keys. However, when I tried to delete a row in the first table I saw this error: DELETE FROM [dbo].[Names_DB]WHERE [LName_Name]=N'andrews' Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_LName_Name'. The conflict occurred in database 'MainDB', table 'Category_A', column 'LName_Name'. I went to the very last table in the sequence and I was able to delete the row without problems, but it did not effect any of the other tables. Please advise. I need to make many changes in these tables, should I use a trigger instead, if so what is the code to trigger each table? I am new to triggers. Thanks Regards Lynn
View Replies !
Only One Of My SQL Delete Statement Are Firing
Hey guys... I am trying to tidy up my code a bit and have one SQL command (Sub class) to call when needing to insert, update, delete and select. I have got one class I am testing with that delete from a table support_ticket and then calls RunSQL() again to delete the corresponding tickets in Support_Reply. however it only seems to want to delete from one table at a time...as i commented out the first sql and it worked and the second fires...but if the first one is active it doesnt fire. Do anyone on the forum know why this has happened? Sub DeleteUserTicket(sender as Object, e as EventArgs) Dim strSQL1 = "DELETE FROM Support_Ticket WHERE (TicketID = " & txtticketID & ")" RunSQL(strSQL1) strSQL1 = "DELETE FROM Support_Reply WHERE (TicketID = " & txtticketID & ")" RunSQL(strSQL1) End Sub 'One class to run the sql statements for the entire page this will reduce in repetitve code as well as reduce code size Sub RunSQL(strSQL) Dim objCmd As SqlCommand Dim sqlConn = New SqlConnection(strConn) objCmd = New SQLCommand(strSQL, sqlConn) objCmd.Connection.Open() 'Check that the rows can be deleted if not then return a error. Try objCmd.ExecuteNonQuery() response.redirect("ticketsystemtest2.aspx") Message.InnerHtml = "<b>Ticket " & txtticketID & " Closed</b> <br/>" Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else Message.InnerHtml = "ERROR: Could not update record, please " _ & "ensure the fields are correctly filled out <br>" & ex.Message & " " & ex.Number Message.Style("color") = "red" End If End Try objCmd.Connection.Close() sqlConn = nothing objcmd = nothing End Sub cheers
View Replies !
Tuning A Delete Statement
Hi, I need to delete the following records (from enrollment_fact): SELECT a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID" FROM "dbo"."ENROLLMENT_FEX2" b, "dbo"."LOCATION_DIM" c LEFT OUTER JOIN "dbo"."ENROLLMENT_FACT" a on c."LOC_SID" = a."LOC_SID" WHERE b."LOC_KEY" = c."LOC_KEY" and a."DATE_SID" between b."MIN_DATE" and b."MAX_DATE" This is the approach (excuse the misuse of the concat function, but you get the idea) DELETE FROM "dbo"."ENROLLMENT_FACT" WHERE CONCAT (a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID") IN ( SELECT DISTINCT CONCAT (a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID" ) FROM "dbo"."ENROLLMENT_FEX2" b, "dbo"."LOCATION_DIM" c LEFT OUTER JOIN "dbo"."ENROLLMENT_FACT" a ON c."LOC_SID" = a."LOC_SID" AND a."DATE_SID" BETWEEN b."MIN_DATE" AND b."MAX_DATE") comments? better way? (without using an sp) thanks
View Replies !
Delete Statement Using A Join Plz Help
well i have 2 table one name detcom and another entcom stored in DB1 the key for both to join on is lets say A, B, C . I need to check if there are records based on the key A, B, C of both table where C EQUALS to '80_300_113' and if there are delete them and then grab data from another database named DB2 on same server (same instance) wich contains the same tables entcom and detcom and insert all the data from those tables into the same tables in DB1 based on the key and where C = '80_300_113' PLZ help
View Replies !
Question About Delete Statement
Hi,My question is : CREATE TABLE t1 (c1 INTEGER, c2 INTEGER, c3 DECIMAL(15,0 )) INSERT INTO t1 VALUES (1, 2, 3.0) How can i define a SQL commend will cause C1 to be decremented each time a row is deleted from the T2 table? Thanks !
View Replies !
Delete Statement Won't Work!!
i am having problem running a simple delete statement against a table. it just hangs is there anything i should look at? the table has 4 primary keys and the index makes up of the 4 keys and ideas? i viewed the delete statement with the execusion plan and this is what i saw. delete -> index delete/delete -> sorting the input -> table delete/delete -> Top -> Index scan.
View Replies !
Recover After Delete Statement
One of our developers just deleted a ton of records- is there any way we can recover this data? (we can't use a backup since a ton of changes were made since we last backed up) Thanks- Jack
View Replies !
Delete Statement Very Slow
Hi, I've got a table with about 500 000 records and growing monthly by about 40 000 records When I perform the following query: DELETE from [myTable] WHERE Month = '07' AND Year='2005' This query will take about 10 minutes to execute. Columns Month & Year are both indexed. Surely MSSQL can't be this slow on only 500 000 records. Must I do some other database optimization ??? Thanks
View Replies !
Join In A Delete Statement???
Hi all, I need to do a delete statement that removes rows from table 2, where data was created before data in table 1. Here's my select statement that shows me the results that meet the criteria: Select DP_ACC.ACC_NO, DP_EVENT.EVENT, DP_ACC.CALL_DATE, DP_EVENT.EVENT_DATE1 FROM DP_ACC Inner Join DP_EVENT on DP_ACC.ACC_NO = DP_EVENT.ACC_NO Where CONVERT(DATETIME, DP_EVENT.EVENT_DATE1) < CONVERT(DATETIME, DP_ACC.CALL_DATE) How do I change this into a delete statement to remove the rows from DP_EVENT?
View Replies !
Please Help Me With A Complex DELETE Statement
Hello, currently I have a query like this: PHP Code: SELECT * FROM relations INNER JOIN paths ON relations.path = paths.path_id WHERE (paths.links = '161') AND (relations.node1 = 162) OR (paths.links = '161') AND (relations.node2 = 162) OR (paths.links = '162') AND (relations.node1 = 161) OR (paths.links = '162') AND (relations.node2 = 161) OR (paths.links LIKE '162%') AND (relations.node1 = 161) OR (paths.links LIKE '%162') AND (relations.node2 = 161) OR (paths.links LIKE '161%') AND (relations.node1 = 162) OR (paths.links LIKE '%161') AND (relations.node2 = 162) OR (paths.links LIKE '%161;162%') OR (paths.links LIKE '%162;161%') ORDER BY relations.node1 Don't pay attention to the 161 and 162 things, is just test data, now my problem is that I want to transform that into a DELETE statement, but I can't find the right way to do it, so far I managed to do something like: PHP Code: DELETE relations FROM relations INNER JOIN paths ON relations.path = paths.path_id WHERE (paths.links = '161') AND (relations.node1 = 162) OR (paths.links = '161') AND (relations.node2 = 162) OR (paths.links = '162') AND (relations.node1 = 161) OR (paths.links = '162') AND (relations.node2 = 161) OR (paths.links LIKE '162%') AND (relations.node1 = 161) OR (paths.links LIKE '%162') AND (relations.node2 = 161) OR (paths.links LIKE '161%') AND (relations.node1 = 162) OR (paths.links LIKE '%161') AND (relations.node2 = 162) OR (paths.links LIKE '%161;162%') OR (paths.links LIKE '%162;161%') But that would delete only from the relations table and not from the paths table. I need to delete from both tables. Can anyone help me please? Its kinda urgent. Thansk!
View Replies !
Delete Statement Question
I have a simple database program which uses the following statement to delete rows by a date range. In testing the SQL statement, it worked, but when using it in the VB program, it says it worked, but doesn't remove the row. I am using a local database. Please help. cmdDeletePayments.CommandType = Data.CommandType.Text cmdDeletePayments.CommandText = "DELETE Payments WHERE (CheckDate BETWEEN @BeginningDate AND @EndingDate)" cmdDeletePayments.Parameters("@BeginningDate").Value = CType(dtpBeginning.Text, Date) cmdDeletePayments.Parameters("@EndingDate").Value = CType(dtpEnding.Text, Date) Try conPayments.Open() iRows = cmdDeletePayments.ExecuteNonQuery() conPayments.Close() Catch ex As Exception MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try MessageBox.Show(iRows.ToString + " records deleted.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
View Replies !
512 Error In Delete Statement
I have a piece of code that uses the db-library with sql server 2000/2005 and runs the following delete statement: DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some Val2' ) AND Col3 IN( integer1 ) AND Col4 IN( integer2 ) AND Col5 IN( 'Some Val3' ) on TABLE1, uploads data into TABLE1 through bulk loading, calls a stored procedure that uses the data, and then deletes the data through the SAME delete statement with EXACTLY the same parameter values. The first delete statement is always successful, but the second statement intermittently gives the following error: 0,0,MS SQL Server Message : SQL Server message 512, state 1, severity 16: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. SQL Server message 3621, state 0, severity 0: The statement has been terminated. Note: I was initially using the equality operator instead of the IN operator in the query but that gave the same results. Can somebody tell me whats going wrong here? I can easily ignore this error because my work is done after the stored proc but I fear amassing a lot of useless data in the table over time. Also http://support.microsoft.com/kb/195491 talks about a case where the delete statement is actually successful but still causes an error when using ADO.
View Replies !
Insert And Delete In One Statement
i am creating an insert based on a select statement -- i need to delete the row from the select statment table after it has been inserted something like insert into table_insert(value1, value2) (select table_exclude_id, value1, value2 from table exclude) delete from table_exclude where table_exclude_id in "the select statement" can you do this?
View Replies !
Delete Statement In Function
Is it possible to create a function that deletes records from a table ? CREATE FUNCTION F_TSImported_Delete() returns int as Begin delete from ts_imported return 0 end GO This throws error like this: Invalid use of side-effecting or time-dependent operator in 'DELETE' within a function.
View Replies !
Very Tricky Delete Statement
I have a rather tricky SQL delete query I’m trying to perform, preferably without cursors. I have the following data set, where I want each of the first changes to the status. IDStatus 1Good 2So-So 3So-So 4Bad 5Bad 6Bad 7Bad 8Bad 9So-So 10Good So given the above data set I would want to delete the following IDs: 3, 5, 6, 7, and 8. As they not the first in order. The problem is that while I can get isolate and save first instance of each status (in this case 1, 2, and 4), I can’t get the what I want. Is there a SQL way of accomplishing this.
View Replies !
Crazy SQL Statement Help Needed... Converting The Rows To Columns (sort Of)...
This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).Employees table:empNameJane DoeAlton BrownJohn DoeTrainingCourse table:courseNameWeldingBrain SurgeryScuba DivingResults table:empName: courseName: completeDate:Jane Doe Welding 2/2/2002Jane Doe Brain Surgery 3/7/2005Alton Brown Scuba Diving 9/23/2004Alton Brown Welding 11/4/2004John Doe Brain Surgery 6/14/2003End result of select statement: Welding Brain Surgery Scuba DivingJane Doe 2/2/2002 3/7/2005 Alton Brown 11/4/2004 9/23/2004John Doe 6/14/2003 Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.
View Replies !
Delete Statement With Parameters Not Working
When I debug my code I see the string going into the parameter correclty, but the the delete statement doesnt work and I'm not sure why. Does this look ok? // Set up SqlCommand, connection to db, sql statement, etc. SqlCommand DeleteCommand = new SqlCommand(); DeleteCommand.Connection = DBConnectionClass.myConnection; DeleteCommand.CommandType = CommandType.Text; // Store Primary Key photoID passed here from DeleteRows_Click // in a parameter for DeleteCommand SqlParameter DeletePrimaryKeyParam = new SqlParameter(); DeletePrimaryKeyParam.ParameterName = "@PhotoID"; DeletePrimaryKeyParam.Value = photoID.ToString(); // Insert new parameter into command object DeleteCommand.Parameters.Add(DeletePrimaryKeyParam); // Delete row, open connection, execute, close connection DeleteCommand.CommandText = "Delete From Photo_TBL where PhotoID IN (@PhotoID)"; Response.Write(DeleteCommand.CommandText); // DeleteCommand.Connection.Close(); DeleteCommand.Connection.Open(); DeleteCommand.ExecuteNonQuery(); DeleteCommand.Connection.Close();
View Replies !
Is The Sql Delete Statement Case Sensitive?
I need to delete a record from a SQL server database table. I need to target a specific column with a query that looks like the following: delete from NameTable where Name = 'Barbosa' The problem is that their are two other records with the name spelled similarly, only differing by case sensitivity. Like as follows: 'Barbosa' 'barbosa' 'BarBosa' Is sql case sensitive? Will my Sql delete statement only delete the record with the word 'Barbosa' in that column. Or will it delete all of the records with the names 'Barbosa' 'barbosa' 'BarBosa'
View Replies !
SQL Data Source Delete Statement
Hey everyone, Currently I have a problem that with a gridview delete command because the database I currently want to delete from is a PK to 1 other table. So how can I delete from that "Membership" table before I use the gridview to delete from the "Events" table? Here is a very shortened version of the tables Events EventID (PK) Membership MembershipID EventID (FK) UserID (FK) I have to delete ALL the members from the event in the membership table first before I can delete the actual Event...So how can I do that when the gridview only allows you to delete from 1 table, if I understand this correctly. Here is the sqldatasource delete command as of right now. DeleteCommand="DELETE FROM [Events_Events] WHERE [EventID] = @original_EventID" Obviously I am getting a error because of the FK. Thanks, Chris
View Replies !
Update And Delete Statement Issues
I'm working on a address book where customers can add, edit and delete address book entries. For the life of me I can't figure out what I'm messing up with the Update and Delete statements of this feature. Can someone please help me.Here's my code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:kalistadbConnectionString %>" DeleteCommand="DELETE FROM [Address] WHERE [AddID] = @original_AddID AND [AddNick] = @original_AddNick AND [AddFN] = @original_AddFN AND [AddLN] = @original_AddLN AND [AddCompany] = @original_AddCompany AND [AddAddress] = @original_AddAddress AND [AddCity] = @original_AddCity AND [AddProv_State] = @original_AddProv_State AND [AddPostal_Zip] = @original_AddPostal_Zip AND [AddCountry] = @original_AddCountry AND [AddPhone] = @original_AddPhone AND [CustID] = @original_CustID" InsertCommand="INSERT INTO [Address] ([AddNick], [AddFN], [AddLN], [AddCompany], [AddAddress], [AddCity], [AddProv_State], [AddPostal_Zip], [AddCountry], [AddPhone], [CustID]) VALUES (@AddNick, @AddFN, @AddLN, @AddCompany, @AddAddress, @AddCity, @AddProv_State, @AddPostal_Zip, @AddCountry, @AddPhone, @CustID)" OldValuesParameterFormatString="original_{0}" onselecting="SqlDataSource1_Selecting" OnInserting="SqlDataSource1_Inserting" SelectCommand="SELECT * FROM [Address] WHERE ([CustID] = @CustID)" UpdateCommand="UPDATE [Address] SET [AddNick] = @AddNick, [AddFN] = @AddFN, [AddLN] = @AddLN, [AddCompany] = @AddCompany, [AddAddress] = @AddAddress, [AddCity] = @AddCity, [AddProv_State] = @AddProv_State, [AddPostal_Zip] = @AddPostal_Zip, [AddCountry] = @AddCountry, [AddPhone] = @AddPhone, [CustID] = @CustID WHERE [AddID] = @original_AddID AND [AddNick] = @original_AddNick AND [AddFN] = @original_AddFN AND [AddLN] = @original_AddLN AND [AddCompany] = @original_AddCompany AND [AddAddress] = @original_AddAddress AND [AddCity] = @original_AddCity AND [AddProv_State] = @original_AddProv_State AND [AddPostal_Zip] = @original_AddPostal_Zip AND [AddCountry] = @original_AddCountry AND [AddPhone] = @original_AddPhone AND [CustID] = @original_CustID"> <SelectParameters> <asp:Parameter Name="CustID" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_AddID" Type="Int64" /> <asp:Parameter Name="original_AddNick" Type="String" /> <asp:Parameter Name="original_AddFN" Type="String" /> <asp:Parameter Name="original_AddLN" Type="String" /> <asp:Parameter Name="original_AddCompany" Type="String" /> <asp:Parameter Name="original_AddAddress" Type="String" /> <asp:Parameter Name="original_AddCity" Type="String" /> <asp:Parameter Name="original_AddProv_State" Type="String" /> <asp:Parameter Name="original_AddPostal_Zip" Type="String" /> <asp:Parameter Name="original_AddCountry" Type="String" /> <asp:Parameter Name="original_AddPhone" Type="String" /> <asp:Parameter Name="original_CustID" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="AddNick" Type="String" /> <asp:Parameter Name="AddFN" Type="String" /> <asp:Parameter Name="AddLN" Type="String" /> <asp:Parameter Name="AddCompany" Type="String" /> <asp:Parameter Name="AddAddress" Type="String" /> <asp:Parameter Name="AddCity" Type="String" /> <asp:Parameter Name="AddProv_State" Type="String" /> <asp:Parameter Name="AddPostal_Zip" Type="String" /> <asp:Parameter Name="AddCountry" Type="String" /> <asp:Parameter Name="AddPhone" Type="String" /> <asp:Parameter Name="CustID" /> <asp:Parameter Name="original_AddID" Type="Int64" /> <asp:Parameter Name="original_AddNick" Type="String" /> <asp:Parameter Name="original_AddFN" Type="String" /> <asp:Parameter Name="original_AddLN" Type="String" /> <asp:Parameter Name="original_AddCompany" Type="String" /> <asp:Parameter Name="original_AddAddress" Type="String" /> <asp:Parameter Name="original_AddCity" Type="String" /> <asp:Parameter Name="original_AddProv_State" Type="String" /> <asp:Parameter Name="original_AddPostal_Zip" Type="String" /> <asp:Parameter Name="original_AddCountry" Type="String" /> <asp:Parameter Name="original_AddPhone" Type="String" /> <asp:Parameter Name="original_CustID" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="AddNick" Type="String" /> <asp:Parameter Name="AddFN" Type="String" /> <asp:Parameter Name="AddLN" Type="String" /> <asp:Parameter Name="AddCompany" Type="String" /> <asp:Parameter Name="AddAddress" Type="String" /> <asp:Parameter Name="AddCity" Type="String" /> <asp:Parameter Name="AddProv_State" Type="String" /> <asp:Parameter Name="AddPostal_Zip" Type="String" /> <asp:Parameter Name="AddCountry" Type="String" /> <asp:Parameter Name="AddPhone" Type="String" /> <asp:Parameter Name="CustID" /> </InsertParameters> </asp:SqlDataSource>
View Replies !
Index Hint In Delete Statement?
According to what I see in BOL, the following should work: delete from dbo.tbl1 WITH (INDEX(idx_un01)) where tbl1_no = 1 Yet when I syntax check this I get: Msg 1069, Level 15, State 1, Line 2 Index hints are only allowed in a FROM clause. (Please ignore the fact that index hints are unnecessary / a bad idea / etc.)
View Replies !
Alter Statement To Delete The Default Value Set
Hi I want to delete the Default value for a specific column which is set to Null I've used ALTER TABLE SYSTEMS_PATIENT_LOG ALTER COLUMN SYSTEMS_LOGID DROP DEFAULT It is giving error Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'DEFAULT'. plz could any one tell me where I could be wrong
View Replies !
SQL Delete Statement In Relational Model
Hi, I have a large database with complex relations and I just can't figure out how to delete a single record and all its relations across database (not to mention that I can't understand why can't the DELETE statement work just like the SELECT). It would be impossible to let you know the structure through this message, but let's say that I have 5 tables in which there are relations like this (all the tables contains a unique ID for each record, but not all contains info of the main record): Production -> ProductionTypes Sales -> ProductionTypes Production -> Sales Sales -> Personnel Personnel --> Orders Orders --> Financial I hope we agree that if I delete the ProductionID.Production directly, there will be no way to know which Financial.Commission I have to delete because I just can't insert the ProductionID in every table on the database. What do I have to do ? I just couldn't find any solid information regarding this subject on the net. Many many thanks in advance ! George
View Replies !
NewBie: Problems With DELETE Statement
I am trying to delete records in a table if a flag set in a linked table is set, but seem to be having problems with it. Neither of the following work: DELETE FROM DSCar WHERE SerialNum IS NOT NULL AND DSCar.SerialNum=DSOrds.SerialNum AND DSOrds.StatusFlag='3' This reports DSOrds.SerialNum could not be bound DELETE FROM DSCar INNER JOIN DSOrds ON DSCar.SerialNum=DSOrds.SerialNum WHERE DSOrds.StatusFlag='3' This reports Incorrect syntax near the keyword 'INNER' But a similar SELECT statement works fine: SELECT * FROM DSCar INNER JOIN DSOrds ON DSCar.SerialNum=DSOrds.SerialNum WHERE DSOrds.StatusFlag='3' I think it is something simple but am having a real head scratcher. Thanks
View Replies !
|
TRACKER
