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
ADVERTISEMENT
Feb 1, 2008
All,
I need to delete all records from the group-ids associated with metagroup_id=28 who are contact_type='members'. I'm trying the query below but it won't work. Hoping someone can shed some light?
DELETE FROM groupContacts
WHERE contact_id IN
(
SELECT c.contact_id
FROM contacts c INNER JOIN
(
SELECT c.contact_id, c.contact_type, c.fname, c.lname, c.school_id, c.permanent_address_1
FROM contacts c INNER JOIN groupContacts gc ON c.contact_id=gc.contact_id
INNER JOIN groups g ON gc.group_id = g.group_id
INNER JOIN groupMetagroups gmg ON gmg.group_id=g.group_id
INNER JOIN meta_groups mg ON mg.metagroup_id = gmg.metagroup_id
WHERE mg.metagroup_id = 28
AND (c.contact_type = 'prospect')
) AS DrvTbl
ON DrvTbl.fname = c.fname
AND DrvTbl.lname = c.lname
AND DrvTbl.school_id = c.school_id
AND (SUBSTRING(ltrim(rtrim(DrvTbl.Permanent_Address_1) ),1,5) = SUBSTRING(ltrim(rtrim(c.Permanent_Address_1)),1,5) )
WHERE c.contact_type='member'
)
AND group_id IN (select group_id from groupMetagroups where metagroup_id = 28)
View 2 Replies
View Related
Jul 27, 2007
Hello,
I have a db with 4 tables are linked by different fields. I'll setup a general layout for you in hopes of getting some assistance with write a delete query based on a date range. T is table, f is field.
T1.F1 = T3.F1
T1.F2 = T2.F2
T1.F3 = T4.F3
T1.F4 = T2.F4 {DATE}
so as you can see, this will be an embedded delete of some type but I have to somehow cache the deleted values so that these values can be used for the parent delete (I think).
I'm new to this stuff so...let me know if my approach is wrong. Perhaps it was a poorly written db. I have to purge about 5GB worth of data.
Thanks in advance,
Sid.
View 2 Replies
View Related
Sep 3, 2006
I need some help on how to structure a sql statement. I am creating a membership directory and I need the stored procedure to output the Last Name, First Name (and if married) Spouse First Name. Like this Flinstone, Fred & Wilma All members are in one directory linked by two fields. [Family ID] all the family members have the same family id and then there is a Family position id that shows if they are the Husband, Wife or Kids. I have no problem with this part select (LastName + ',' + FirstName) as Name, [Phone 1] as Phone, [Unit Name] as WD, [Street 1] as Street, SUBSTRING(City,1,3) as City, SUBSTRING(Postal,1,5) as Zipfrom Membership Where [HH Order]=1 Order By LastName ASC Could someone help me on how to display the " & Spouse FirstName " as part of the name field only if there is a spouse [HH Order]=2 for the current [Family ID]????
View 6 Replies
View Related
Nov 13, 2006
I need to get multiple values for each row in a database, then do a calculation and insert the calculation and the accountnumber related to the calculation the data, into a different column. I get an error trying it this way...there is no real identifier, it is jsut something that needs to get done per row...any ideas on how I can accomplish this?
Declare @NetCommission decimal
Declare @AccountNumber varchar(50)
Set @NetCommission = (select (CommissionRebate * Quantity)
from Account A
Join Trades T on A.AccountNumber = T.AccountNumber)
Set @AccountNumber = (select A.AccountNumber
from cmsAccount A
Join Trades T on A.AccountNumber = T.AccountNumber)
Insert into Transaction
(
Payee
,Deposit
,AccountNumber
)
Values
(
'Account Credit'
,@NetCommission
,@AccountNumber
)
View 13 Replies
View Related
Jan 15, 2004
i have a complex sql statement and i think that my structure looks good but apparently not because i keep getting the same error, i was wondering if anyone knew how to correct this problem.
SELECT A.*, B.Name, C.SIName, D.IID,
(Select [LastName] , [FirstName]
FROM E INNER JOIN F ON E.SID =
F.SID , A
WHERE F.Emp='Service' AND E.Lead=1 AND E.ID=[A].[D])
AS Service,
(Select [LastName] , [FirstName]
FROM E INNER JOIN F ON E.SID =
F.SID ,A
WHERE F.Emp='Industry' AND E.Lead=1 AND E.ID=[A].[D])
AS Industry
FROM A , B, C
WHERE (1=1) AND B.SID = C.SID AND A.ID = B.ID
i always get this error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
does anyone know how to fix this query?
View 9 Replies
View Related
Mar 18, 2004
Belows are the data of table T1
field1 field2 field3 Value
F1 F2 F3 A
F1 F2 F3 B
F1 F2 F3 C
... .... .....
F1 F2 F3 Z
Can any bright person help to script the SQL to extract above data set and present as below ???
field1 field2 field3 Value
F1 F2 F3 ABC......Z
Thanks for help
View 2 Replies
View Related
Oct 9, 2006
I have this stored procedure that returns a rowid, distance. It has a latitude, longitude, and range as inputs, it takes the latitude and longitude and computes a distance with every lat/long in a table PL_CustomerGeocode. Once that distance is computed it compares that distance with the range, and then returns the rowid, distance if the distance is <= range. I have the SELECT statement down, but now i just need to enter this information into a seperate table PL_Distance with (rowid, distance) as columns. The sql statement is as follows, and i cant figure out where the rowid part is an the distance part is: DECLARE @DegreesToRadians float SET @DegreesToRadians = Pi()/180 SELECT rowid, Cast(distance As numeric(9,3)) AS distance FROM (SELECT rowid, CASE WHEN @srcLat = geocodeLat And @srcLong = geocodeLong THEN 0.0 WHEN ABS(Arc) > 1 THEN 0.0 ELSE 3963.1 * 2 * asin(Power(Arc, 0.5)) END AS distance FROM (SELECT Power(sin(DLat/2),2) + cos(@srcLat*@DegreesToRadians)*cos(geocodeLat*@DegreesToRadians)*Power(sin(DLong/2),2) AS Arc, rowid,geocodeLat,geocodeLong FROM (SELECT @srcLong*@DegreesToRadians-geocodeLong*@DegreesToRadians AS DLong, @srcLat*@DegreesToRadians-geocodeLat*@DegreesToRadians AS DLat, rowid, geocodeLat, geocodeLong FROM dbo.PL_CustomerGeoCode) AS x) AS y) AS z WHERE distance <= @range
View 1 Replies
View Related
Aug 30, 2007
Hi All,
My sql is a little rusty, i ve been trying to do few things but still no luck. I m trying to query some data in one column based on certain . Here is my puzzle:
I have 7 tables: categories, characteristics, configs, rm_cat, rm_chars, rm_conf and rooms.
And here are the details on these tables:
- categories: {cat_id, cat_name}
- characteristics: {char_id, char}
- configs: {conf_id, conf}
- rm_cat: {room_id, cat_id}
- rm_chars: { room_id, char_id}
- rm_conf: {room_id, conf_id}
- room: {room_id. room_name}
I m trying to select a "room_name" based on a certain cat_id, char_id and conf_id and i don't know how to do this.
Sincerely,
View 3 Replies
View Related
Nov 12, 1998
I have what is turning out to be a very complex T-SQL query to build.
I'm porting an App from Access to SQL Server... one of the Access queries used a function made in VBA to return a value.
For the life of me, I can't figure out how to make this work using only SQL Statements.
I'm not even sure how to even ask this. So here I go.. I've really simplified the SQL statement to help out with this. There are initially two tables.
Container and TraceRecord
Container is a table of Cargo Containers (the truck trailers you see on the highways)
TraceRecord is a table of location records as the containers move from city to city on the railroad.
The Containers move on fixed routes (ie.. Long Beach to Chicago, Long Beach to New York, etc...).. in the Container table there is a field called Route which records which Route the container is moving on and is related to a table of routes which I'll get to later.
[This is the old Access query.. notice the IIF statement and the function call to "IsOnTime"]
SELECT c.ContainerID, c.IngateDate, t.Location, t.Status, t.EventDate, t.EventTime
IIf(IsNull(c.IngateDate) Or IsNull(t.Location),"No Ingate Rail Record Captured",IsOnTime(t.Location,t.Status,t.Rail,c.Ro ute,c.IngateDate,t.EventDate, t.EventTime)) AS RailSch,
t.Rail
FROM c Container LEFT JOIN t TraceRecord ON c.ContainerID = t.UnitNumber
For each route there is a scheduled travel plan.
Example.. when a container is taken to the BNSF railroad in Long Beach that is called an "Ingate" and is considered Day 0 (zero). As the container moves on the railroad from Long Beach to Chicago, it will pass through other cities, and the TraceRecord table will record where the container is and what time and day.
What I need to do is determine, based on the latest TraceRecord record, by how many hours is the Container "on time".
There is a routing table which lists the predefined travel path for each route.. listing the number of days and hours a container should be a certain place since the day the container was taken into the railroad at the origin "Ingate".
What the IsOnTime function did was take the arguments and do some math with the routing tables and find out how many hours a unit is or is not on time.
Here's a copy of the function from Access 97 using DAO. I don't know if any of this is going to make sense to anyone.. but I'm stuck and don't know what else to do.
Function IsOnTime(strLocation As String, strStatus As String, strRail As String, intRoute As Integer, _
dateIngateDate As Date, dateCurrentDate As Date, dateCurrentTime As Date) As Variant
On Error GoTo errorh:
Dim rs As Recordset 'Rail Schedule Recordset
Dim db As Database 'Current Database
Dim sqlFind As String 'Search String to find city transit time
Dim dateScheduleDateTime As Date
Dim dateDifference As Integer 'Hours difference between trace and schedule
Dim varvar As Variant
If dateIngateDate = Null Then
IsOnTime = -9999
Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("tTransitTimeTable", dbOpenSnapshot)
sqlFind = "([RouteID] = " & intRoute & " AND [City] = '" & strLocation & "' AND [StatusCode] = '" _
& strStatus & "' AND [Rail] = '" & strRail & "')"
rs.FindFirst sqlFind
If rs.NoMatch = False Then
If (rs!daymarker = "" Or IsNull(rs!daymarker)) And (IsNull(rs!daymarker) Or rs!cutoff = "") Then
IsOnTime = "Finished"
rs.Close
Exit Function
End If
dateScheduleDateTime = DateAdd("d", rs!daymarker, (dateIngateDate + rs!cutoff))
dateDifference = DateDiff("h", dateScheduleDateTime, (dateCurrentDate + dateCurrentTime))
If dateDifference <> 0 Then
IsOnTime = -dateDifference
End If
If dateDifference = 0 Then
IsOnTime = 1
End If
Else
IsOnTime = -9999
End If
rs.Close
End If
Exit Function
View 1 Replies
View Related
Jul 23, 2004
There are 3 tables, VendorLists, Vendors, and Referrals.
VendorLists is a linking table. It has VendorListID, VendorID, and ListID fields.
Vendors is linked to VendorLists through the VendorID field (one to many)
Referrals is linked to VendorLists through VendorListID (one to many)
I'm given a value for ListID and have to pull records from both the Vendors and Referrals table (a referral is a description of a vendor, one to many).
I am able to do this with the following SQL select statement:
SELECT Referrals.Description, Vendors.Company
FROM Referrals CROSS JOIN Vendors
WHERE Referrals.VendorListID IN
(SELECT VendorListID FROM VendorLists WHERE (ListID = lid))
AND
(Vendors.VendorID IN (SELECT VendorID FROM VendorLists WHERE ListID = lid))
ORDER BY Vendors.VendorID
This pulls all the appropriate records and values that i need and orders them by the identifier for the vendor. However, I want to randomly order the vendors but still group them together by company, so, if the VendorID is 1 for "joe's crab shack" and 2 for "billy's ice cream shop", the above will always list joe's crab shack first and all it's referrals. i want to be able to randomly order the vendors, but still keep the referrals of those vendors grouped together so that when i iterate over them, they're grouped.
Does anyone have any idea how to do this? I'm stumped!!
View 4 Replies
View Related
Feb 24, 2004
TABLE : USER
USERID
1
2
TABLE : TIME
TIMEID|USERID|RT|OT|DOT|DATE
1|1|8|2|1|2004-02-01
2|1|8|2|0|2004-02-02
3|2|8|0|0|2004-02-01
4|2|8|2|2|2004-02-02
RT : Regular Time
OT : Over-Time
DOT : Double Over-Time
I need to write a query to display the results in this way
USERID|DATE|TIME
1|2004-02-01|8
1|2004-02-01|2
1|2004-02-01|1
1|2004-02-02|8
1|2004-02-02|2
2|2004-02-01|8
2|2004-02-02|8
2|2004-02-02|2
2|2004-02-02|2
basically, the time entries for each user each day , seperate rows for RT, OT, DOT if they are not equal to 0
ive been breaking my head on this for quite a while. any help is appreciated.
thanks
View 5 Replies
View Related
Jul 20, 2005
Here is my SQL string:"SELECT to_ordnum, to_orddate," _& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ONDDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ONDOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from resultof outer select) AS Total" _& "FROM DDTORD WHERE to_trak2id IN (39, 40, 41) AND to_ordtype = 's'AND to_status = 'c' GROUP BY to_ordnum, to_orddate ORDER BY to_ordnumDESC"The outter Select statement returns various amounts of order numbersrepresented by 'to_ordnum' in the outer Select clause which has tomeet the critera in the outer WHERE clause. I would like to placethese numbers selected into the inner WHERE clause for the innerselect statement where DOMBOM2.b2_ordnum = ?the order selected byouter select statement.I have tried placing to_ordnum into that location but the SQL2000server does not process it.Any suggestions, ideas?Thank you,Brett
View 1 Replies
View Related
Jul 24, 2006
I have a pretty complex SQL statement that looks like this:
SELECT aspnet_Employers.active, aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title AS Contact, SUM(aspnet_Employers.EmployeeCount) AS [# Emps], COUNT(aspnet_Signups.account) AS [# Email Addresses], COUNT(aspnet_ContactMe.username) AS [# Contact Me], COUNT(aspnet_AppsSubmitted.account) AS [# Apply Now]FROM aspnet_Employers LEFT OUTER JOIN aspnet_AppsSubmitted ON aspnet_Employers.UserName = aspnet_AppsSubmitted.account LEFT OUTER JOIN aspnet_ContactMe ON aspnet_Employers.UserName = aspnet_ContactMe.username LEFT OUTER JOIN aspnet_Signups ON aspnet_Employers.UserName = aspnet_Signups.accountGROUP BY aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title, aspnet_Employers.active
It does work the way i want it, but the problem is, on my Gridview when i change the Employers accounts "Active" status either way, it changes the username field from the username of the account, to "null".
Why does it do this?
What would i change to prevent this from happening?
Thanks!
View 3 Replies
View Related
Sep 30, 2007
I may not be seeing the forest through the trees here but here goes. I've got a table of computer configurations with columns for cpu, ram, m/b, hdd, etc. The values in those columns are related to the id field in another table named parts. The parts table has columns, id, partnumber, description, and cost. What I want to do is be able to pull a record from the computer configurations table and instead of getting the integers in the cpu, ram, etc. fields I want to put the corresponding description field from the parts table. For example:I want this...id Name CPU RAM MB HDD ... 1 Fast Machine Fast CPU Big RAM Greate MB Huge HDDNOT this....id Name CPU RAM MB HDD ...
1 Fast Machine 1 3 2 7 Below is a screenshot of my actual table relationships.Thanks in advance
View 14 Replies
View Related
Nov 6, 2006
hi.
can somebody explain to me why the below
update fails to update one row and updates
the entire table?
Code:
UPDATE addlist
SET add_s = 1
WHERE EXISTS
(SELECT a.add_s, a.email_address, e.public_name
FROM add a, edit e
WHERE a.email_address = e.email_address
and a.add_email = 'mags23@rice.edu' and a.add_s = 0 and e.public_name = 'professor');
and, what is the solution? thank you.
View 8 Replies
View Related
Apr 29, 2008
I am new to SQL server 2005. I run a large query daily against a teradata warehouse and it populates an access database. I am now attempting to run my query in a SQL 2005 database. I get an error on this case statement:
CASE
WHENCAST ( ( B.zip_cd ( format '99999' ) ) AS char ( 5 ) ) = '*****' THEN SUBSTR ( CAST ( ( B.zip_cd ( format '999999999' ) ) AS char ( 9 ) ), 1, 5 )
ELSECAST ( ( B.zip_cd ( format '99999' ) ) AS char ( 5 ) )
ENDAS ZIP_CD
The error is: Msg 102, Level 15, State 1, Line 229
Incorrect syntax near '99999'.
Any help is greatly appreciated!!!
View 2 Replies
View Related
Nov 8, 2006
hi.I am having probelms with an update statement. every timei run it, "every" row updates, not just the one(s) intended.so, here is what i have. i have tried this with both AND and ORand neither seem to work.i dont know why this is elluding me, but i'd appreciate help with thesolution.thanks.UPDATE addSET add_s = 1WHERE add.add_status = 0 and add.add_email = 'mags23@rice.edu'or add_s in(SELECT a.add_sFROM add a, edit eWHERE a.email_address = e.email_addressand e.public_name = 'professor')
View 22 Replies
View Related
Sep 3, 2007
Hi,
Code Snippet
This is difficult to explain in words, but the following code outlines what I am trying to do:
with myTableWithRowNum as
(
select 'row' = row_number() over (order by insertdate desc), myValue
from
(
select table1Id As myValue from myTable1
union
select table2Id As myValue from myTable2
)
)
select * from myTableWithRowNum
Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?
View 4 Replies
View Related
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
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
Nov 12, 2003
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
View 7 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
