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.
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)
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.
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.
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]????
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)
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.
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
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.
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)
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!!
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
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?
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
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');
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'.
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')
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?
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
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.
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
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
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?
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.
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...)
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
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
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
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)
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.
