Update And Delete Statement Issues

Oct 7, 2007

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 2 Replies


ADVERTISEMENT

INSERT, UPDATE, And DELETE Statement Checkbox Inactive

Mar 26, 2008

Hi  AllgI have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advancehttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPGhttp://img139.a.com/img.php?image=28285_2_122_937lo.JPG   

View 1 Replies View Related

SQL Server 2012 :: MERGE Statement Attempted To UPDATE Or DELETE Same Row More Than Once

Mar 12, 2015

I have created a Dynamic Merge statement SCD2 Store procedure , which insert the records if no matches and if bbxkey matches from source table to destination table thne it updates old record as lateteverion 0 and insert new record with latest version 1.

I am getting below error when I ahve more than 1 bbxkey in my source table. How can I ignore this.

BBXkey is nothing but I am deriving by combining 2 columns.

Msg 8672, Level 16, State 1, Line 6

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

View 4 Replies View Related

SQL Server 2012 :: Delete / Recreate Identity Column / Fetch Newly Created Values In Update Statement?

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

One DELETE Sql Statement To Delete From Two Tables

Aug 12, 2007

I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?

View 5 Replies View Related

Help With Delete Statement/converting This Select Statement.

Aug 10, 2006

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 1 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

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

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

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

SQL Server 2012 :: Update Statement With CASE Statement?

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

Transact SQL :: Update Statement In Select Case Statement

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

UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL

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

JDBC 2005 Update Statement - Failing Multi Row Update.

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

Stored Procedure - Update Statement Does Not Seem To Update Straight Away

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

Delete Statement

Apr 11, 2007

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 1 Replies View Related

Help With DELETE Statement

Nov 13, 2007

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 2 Replies View Related

Sql Delete Statement

Sep 15, 2004

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 10 Replies View Related

DELETE Statement

Dec 14, 2001

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 7 Replies View Related

SQL Delete Statement

Oct 2, 2006

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 3 Replies View Related

DELETE Statement Conflicted

Dec 6, 2006

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 6 Replies View Related

Trigger On Delete Statement

Sep 26, 2007

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 2 Replies View Related

Only One Of My SQL Delete Statement Are Firing

Mar 12, 2006

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 2 Replies View Related

Recover After Delete Statement

Sep 6, 2001

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 2 Replies View Related

Delete Statement Won&#39;t Work!!

Feb 1, 2001

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 1 Replies View Related

Delete Statement Very Slow

Dec 12, 2005

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 6 Replies View Related

Please Help Me With A Complex DELETE Statement

Jan 27, 2005

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 5 Replies View Related

Tuning A Delete Statement

Oct 26, 2004

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 8 Replies View Related

Delete Statement Using A Join Plz Help

Mar 2, 2005

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 1 Replies View Related

Question About Delete Statement

Nov 4, 2007

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 6 Replies View Related

Very Tricky Delete Statement

Apr 29, 2008

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 10 Replies View Related

Writing Delete Statement

Jul 30, 2013

i have problem to discuss regarding delete statement. Due to the bad design of application i have added some duplicate values in table. i have fixed the issue for new insert but how to delete my old bad id. i have table having three column,

ID1 ID2 ID3
101 2 3
102 2 3
103 2 4
104 3 4

in my case first two record are duplicate i want to keep first one and delete the other.

View 8 Replies View Related

How To Delete Row Number On Where Statement

Nov 7, 2013

I have duplicate rows of data with the exact same data, for multiple sets of data.For example,

1 Fred Flintstone
1 Fred Flintstone
2 Barney Rubble
2 Barney Rubble
etc...

If I use the delete and where command, it will delete both rows and I want to keep one of them. I don't want to copy down each one and re insert as there are too many columns for each one and there are too many rows also.I was thinking of a delete row-number command in a where statement. but tried multiple forums and its not working.

View 2 Replies View Related

How To Add Subquery To Delete Statement

Apr 28, 2015

I have to write a delete statement that deletes all customers that have not put in an order I must use a subquery in the exist operator.

View 5 Replies View Related

Delete Statement In Function

Jul 19, 2006

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 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved