1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network.
I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables.
I've tried following query
DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11;
But it gives ' error in query.
View Complete Forum Thread with Replies
Related Forum Messages:
Get List Of Tables Containg Specific Records
I have a database with 20+ tables that contain data from various instruments. Samples can be measured on any combinaiton of instruments and I would like to be able to query the database to find which tables have data for a specific sample, with the table names being returned to build a dynamic web form for selecting and viewing data. I can build sort of brut force queries via php to do this to mitigate re-writing webpages when additional tables are added, but it seems to me there should be an elegant solution (or at least brut elegant) to this.
View Replies !
Duplicate Records Delete Query
I have a table with duplicate records but the key of the duplicate records is different, that is: The key of one of the record has the format: Rssss The key of the second record has the format: ssss How can I delete both records from the table?
View Replies !
Lock Tables To Update And Delete, But Insert New Records
I'm trying to "lock" mysql tables - to prevent rows being updated/deleted - I still want to be able to insert new rows (legal reasons). I have toyed with the idea of changing the user permissions - can I simply change it so that no-one can perform an update on that table, even root? But I feel there must be a simpler way that I and my many web searches am missing.
View Replies !
Delete Against Multiple Tables
SELECT * from clicks, urls WHERE clicks.url=urls.url AND urls.description IS NULL The clicks table has 31 instances of url that match 5 instances of url in the urls table.When I run the following I get a syntax error DELETE * from clicks, urls WHERE clicks.url=urls.url AND urls.description IS NULL
View Replies !
Delete In Multiple Tables
I would like to delete all rows in all tables that contain a certain value. For example, there is an id in several tables and I want to delete all rows in multiple tables where id=8. Is this possible? If so, how do I form the delete query?
View Replies !
Delete From Multiple Tables ..
how do i alter the script below to delete from multiple tables? <?php require("db.php"); $db = mysql_connect("$db_host", "$db_user", "$db_pass"); mysql_select_db("$db_name",$db); mysql_query("DELETE FROM support WHERE prod_code = '$prod_code'",$db); echo "Information Deleted. <a href=admin.php>Click Here</a>"; ?>
View Replies !
Delete From Multiple Tables
Having a bit of a problem deleting from multiple tables (hey, who doesnt!). So I have 3 tables, news, comments and reviews. News has a autoincrement ID as the primary key. This is represented in the comments and reviews table as a field called newsID (comments and reviews have a seperate autoinicrement ID as the primary key). I pass into the query a $nid which is the ID of the news story I want deleted from the news table. mySQL v4.0.21 DELETE FROM news, reviews, comments WHERE news.id = '$nid' AND comments.newsID = '$nid' AND reviews.newsID = '$nid' Unfortunately this dont work and I think its because of the joins. Any advise on how to formulate the sql correctly?
View Replies !
Delete Rows From Multiple Tables
I am trying to delete from multiple tables with one query. Here is the situation: I have 3 tables Boards, Topics, Messages When I delete a row from the boards table, i would like all topics which have the same board id deleted and any messages with the same topicid also deleted. I have tried this query but it only deletes the row in the boards table.
View Replies !
Delete Rows From Multiple Tables (AND & WHERE Clause)
I have a database containing the folllowing tables; PUPIL id ... ROA pupil_id ... MESSAGE id pupil_id ... REPLY message_id ... How can I delete all the rows from all tables containing the same pupil id and all replies to messages from the same pupil? I managed it using AND clauses in WHERE but if there is no data in say the MESSAGE table then it doesn't work.
View Replies !
Delete From Multiple Tables Base On 2 Values..
.. the title pretty much says it all. My experimenting led to two of my tables being dropped so, i'm looking for some help! i need to delete from 3 tables but only the row in each table that has the user column and pic column equal to the values i pass it. I hope my problem description is clear enough.. let me know if it's not!
View Replies !
Deleting Records From Multiple Tables
i am using dreamweaver, php mysql. i have 4 tables and want to delete a record from more than one table , all of them are related. For example client table, product , delivery etc. if client A is removed i want to remove all records of client A from product and delivery.
View Replies !
Counting Records From Multiple Tables
Got a question for you that is really stumping me. I have 3 tables job_jobs ------------ job_id job_name . . . job_cats ------------- cat_id cat_name . . . job_jobsToCats ------------- jtc_id job_id cat_id *************** ok i have those 3 tables to categorise the jobs on the site and im trying to build a query that will essentially get all categry names and display the number of jobs in that category so when i output it i will get accounting (12) admin(2) etc etc
View Replies !
Counting Records In 2 Tables Using 1 Query
I have these 2 queries. SELECT count(*) gifts FROM gift g WHERE g.this and g.that SELECT count(*) events FROM events e WHERE e.this and e.the other thing is there a way to put these into one query..... SELECT count(g.*) gifts, count(e.*) FROM gift g, event e WHERE . . . . so far nothing seems to be working .....
View Replies !
Retrieving Specific Records
I am trying to establish (using a straight MySQL query in version 4.0.18) the level of qualifications someone has, i.e. degree, postgraduate degree or other. To simplify things, I need to use only two tables, namely: master table, containing: staffno (Primary Key); forename; surname; psnquals table, containing: psnqualid (Primary Key); staffno (Foreign Key); levelid (where 1 = degree, 2 = postgraduate degree, 3 = other) In the followning query, staffno 1 has three qualification levels, 1, 2, 3. The query finds the first matching record (where cdelevelid = 1) and then stops, making it appear as if the employee had no postgraduate qualification. One person can have one or all of the above qualifications. I need to write a query that shows if a person has one, two or all of the above. I tried using the following query, but it seems only to look at the first psnqualid record it finds and then stops: SELECT m1.staffno, m1.forename, m1.surname, CASE WHEN p1.cdelevelid = 1 THEN "D" WHEN p1.cdelevelid = 2 THEN "P" ELSE "O" FROM `master` m1 INNER JOIN psnquals p1 ON m1.staffno = p1.staffno I also tried using an IF statement, but this also seemed to stop after finding the first matching record in the psnquals table.
View Replies !
Selecting Specific Records
I have two tables. I want to select the last record for a specific type from one table and look up info from the other table. Is there a command that will select go to the last record of a query?
View Replies !
How To Get Records Between Two Specific Dates?
i'm a newbee to mysql. i'm having a table called transaction which stores transaction details with dates. i'm having another table callled company in which the company info stored, The company table has the fiscal month details. what i wnt to do is get the records from the transaction which is between two specific dates(i.e) in the fiscal year eg: 04-01-2006 to 03-31-2007 as far as now i'm having the query, SELECT t.* FROM transaction t,company c where t.companyid=c.id AND (MONTH(t.transdate)>MONTH(str_to_date(c.fiscalmonth,'%M')) AND YEAR(t.transdate)=YEAR(now())) || (MONTH(t.transdate)<MONTH(str_to_date(c.fiscalmonth,'%M')) AND YEAR(t.transdate)=YEAR(now())+1) and c.id=2 order by transdate Note: the company table stores the fical months as month names(i.e) March etc., i think this is far complex, and is there any easy function to get the records b/w two dates??
View Replies !
Query Across Multiple Tables?
I am new to mysql and need help on how to join tables. I have a database which contains 4 tables, the main table contains information by date order and the other 3 contain data also in date order. So I need to write a query that retrieves all the information for one record, lets say I want to query on the main table any entry that is for the 2004-01-06 and this date is also in a field called 'Date' in the other tables, how do I go about pulling back all the info for that entry across all the tables? I have tried doing this: WHERE main.Date = table2.Date AND table3.Date AND table4.date however it returns loads of data when it should only pull back the one entry.
View Replies !
Query On Multiple Tables
Tables: patient ------- patientID | name scans ------ scanID | scantype | scandate | patientID What I want to do is create a table that has a list of patient names and their latest scandate and scantype I can't quite figure out the syntax
View Replies !
One Query For Multiple Tables
I am using MySQL 4.0 I have 4 tables w/ the same columns. Table1, Table2, Table3, Table4, under database=survey. I want to count how many surveys have been completed under each Table. Using one query, I want the total of each Count(survey_id) from each table. Basically, Select Count(Survey_id) from Table1 + Select Count(Survey_id) from Table2 + Select Count(Survey_id) from Table3 + Select Count(Survey_id) from Table4. HOw can i do this in one query?
View Replies !
Query From Multiple Tables
I want to construct one single query that gets values from three different tables Table "A" has a column "id" Table "B" has columns "topicid", "topic" and "userid" Table "C" has coluns "userid" and "address" A.id links to B.topic_id. B.userid links to C.userid I want to get A.id, B.subject, C.address from all three tables in one query. Due to some other constraint, I must use left join with the first two tables: select A.id, B.topic from A left join B on (A.id = B.topicid); How can modify the above query to get C.address in the same query?
View Replies !
SQL Query Across Multiple Tables
I wish to make a 'Latest Comments' section, but I am unsure of the most efficient query to use. I have comments spread across 6 different tables, named as below; cms_artistcomments cms_featurecomments cms_freshtalentcomments cms_newscomments cms_reviewcomments cms_wallpapercomments Each set up as follows; comment_id artist_id (changes for each table) comment_author comment_post comment_date What I would like is to extract 'so many' number of comments and in reverse date order, to give the latest 'so many' comments.
View Replies !
Get Rows From Multiple Tables In One Query
Is there a way to select rows from multiple tables in one query? Say I have the following tables and columns:Storestore_idItemitem_iditem_store_id I want to get a store by it's id + all the items associated with that store id. Do I have to make two separate queries for this? One to get the store, and another to get all the items for that store.
View Replies !
How To Query Multiple Tables Properly
I tried to use the below code, but its only check the first row in the table only. Its not checking all the rows in a table. PHP Code:  SELECT books.bid,books.desc,books.img FROM books,visitor,stats WHERE visitor.ip !='$vip' AND stats.hits < '1000' AND books.bid = visitor.bid AND books.bid = stats.bidÂ
View Replies !
Sql Statement Query For Multiple Tables
Ive been trying to put together a php page for the last couple weeks that does a query on multiple tables at 1 time, and returns the results from each. In other words, i put in a search, it searches all the tables, then returns the results from whatever tables it finds my data in. each table is extremely different from the other, and as is the data. Im pretty sure that its just my statement that is wrong. Here is my statement but I cant seem to get it working right. select 'table1' results from table1 where field1= '$var' or field2 = '$var' union select 'table2' results from table2 where field1 = '$var' union select 'table3' results from table3 where field1 = '$var' $var is my search that is put in.
View Replies !
Fulltext Query On Multiple Tables?
I've built a database for an organisation, in it are 3 tables i need to include in a search, news, events, and locations. Each have a title and a description. What I've been trying to achieve (and failed at, hence me running out of time) is to have the user enter one search query, and the results page, paginated, returning matching results from all 3 tables, which will then be listed in the search as "News: ..." etc, and the plan was to order them by the fulltext score. I considered running three consecutive fulltext queries and combining them in PHP, but then I won't be able to limit the result sets for paginating them.
View Replies !
Joining Multiple Tables In One Query
I was wondering if anyone could see the best way to lay this functionality out. I have the following tables and fields (comma seperated part). Assume the vendors userid = 5. I want to grab a list of all users that are within that vendors territory. [table] users userid, zipcode * multiple users per single zip code [table] vendors userid, vendorid [table] assoc_zipter zipcode, territory * multiple zip code per single territory [table] assoc_venter vendorid, territory * multiple territories per single vendorid Here is the SQL written out. SELECT * FROM users WHERE * vendors.vendorid=5 * vendors.vendorid = assoc_venter.vendorid * assoc_venter.territory = assoc_zipter.territory * assoc_zipter.zipcode = users.zipcode I am trying to get this all in one sql statement. Here is a sql statement that does not work: SELECT * FROM users JOIN vendors ON (vendors.userid=5) JOIN assoc_venter ON (vendors.vendorid=assoc_venter.vendor) JOIN assoc_zipter ON (assoc_venter.territory=assoc_zipter.territory AND users.zipcode=assoc_zipter.zipcode) GROUP BY users.userid
View Replies !
Get Records By Line
I am dumping my data like this mysql -u root -p --tab=c:folderdata --fields-terminated-by"|" --lines-terminated-by=" " databasename tablename But the data i get is separeted by the "|" but the records are separeted by this strange character which i can not copy here for some reason but it is a square character between each record.
View Replies !
How To Query Multiple Tables Based On Value Difference
I 5 tables all with equal columns,but with different values. Per example: time_start,time_end,num_a,num_b,price etc. How can I retrieve the values of all columns `price` between tableX and tableY where time_start in table Y is higher as a datetime than time_start in X. So,the query is about finding the values of `price` between 2 dates in differnt tables.
View Replies !
Query On Multiple Tables Events And Images
I have two tables, events and images. I want to get the data from column image_path based upon two variables, $event_date and $event_id. The date is only in the events table but event_id is a foriegn key in the images table. I'm really struggling with the syntax and have got really good at generating errors!
View Replies !
Seemingly Simple FULLTEXT Query Over Multiple Tables
Can't seem to get my head around this query. Here's what I have: I have 2 tables: TABLE a, TABLE b: Table a Columns: UID, FName, LName, Bio Table b Columns: UID, Specialty I want to search these FULLTEXT Indexed table columns (bio, Specialty) from the different tables for a string, and output the resulting UID and Username, one row only per UID (UID matches between tables)... To put it another way, if the search string is found in either of the 2 columns in the 2 tables, I would like to output the UID and FName from TABLE A. SELECT DISTINCT E.UID, E.FName, E.LName FROM contact AS E, brg_uid_specialties AS C WHERE E.UID=C.UID AND MATCH (E.Bio) AGAINST ('bubble noodle Oranges') OR MATCH (Specialties) AGAINST ('bubble noodle Oranges')" The resulting output from my sample tables is an enormous dataset (when it should only get 1 hit), and I can't figure out why... .
View Replies !
Selecting Records Belonging To Multiple Records Through Join Table
I have is 3 tables, 'categories', 'levels' and 'categories_levels', the 'categories_levels' table is a join table, with the `id`, `category_id` and `level_id` fields. Categories can belong to many different levels, so records in the join table might look like this (assuming that the category has an ID of 5) id, category_id, level_id 1, 5, 1 2, 5, 2 3, 5, 3 So you can see that category 5 belongs to 3 different levels (1, 2, and 3). On a web page, I've got a multiple select box, which allows a user to filter the categories depending on which levels they belong to. What I need a query to do is use that join table to find all categories which belong to the selected levels. Now for the tricky bit. Only the categories which belong to every single selected level should be displayed. So in the example above, if I select level 1, then category 5 will be displayed, but if I select levels 1 and 4, then category 5 should not be displayed, because although it does belong to level 1, it doesn't belong to level 4 as well.
View Replies !
Syncronising Specific Tables
I would like to ask you a question regarding a problem that I experience. I have to syncronize two databases, one MySQL and one MS-SQL. Unfortunately, I am not aware of MS-SQL databse but a friend of mine is. So, he told me that he can export data to a mySQL database, which will contain three diverse tables: one with new products added, one with the deleted products, and one with an update of the current products. Is there a way the initial (web-based) MySQL Database to read the exported database and make the necessary adjustments related to the each table (table 1: adding, table 2: deleting, table 3: updating). How can this procedure be done?
View Replies !
Excluding Specific Tables In Mysqldump
I have a db with 84 tables running on MySQL 3.23.52. I want to clone about 81 of the tables and exclude the last 3. Two of these 3 are huge, so it would speed up the cloning conisderably (and keep the server admin folks happier). I looked in my MySQL textbook and didn't see any good options. Is there a simple way of excluding specific tables using mysqldump?
View Replies !
Query A Specific Day, And GROUP BY
The query below, counts how many records each agent has entered in the previous 7 days and groups them by agent... Then it also counts a total for all the agents for the previous 7 days as well, not grouped. How can i query a specific day of the week, say 4 days back, only count that days records and group them by agent?
View Replies !
|
TRACKER
