MYSQL :: Compare And Delete Or Insert Within Tables

Compare 2 Tables And Delete
i made a rookie mistake in my delete user code and now i got a problem that i still got challenges by the user in my database with the deleted user_id

i have two tables set like so

Table users contains one cell called user_id
Table challenges contains two cells called challenge_to and challenge from - each contain a user_id

i need to get all the user_id `s from the users table and then delete all challenges that contain a user_id not in the list

How About The Speed Compare Connection/query/insert And Other Between C,c++,php ?
How about the speed compare connection/query/insert and other between c, c++, php?

Compare Tables
Does anyone know if there is any tool that can compare two databases (and the tables) and allow you to create the SQL necessary to make them identical? I.e. creating new tables, adding columns,

Compare Two Tables
I have two tables.

First table is just list of dealers (table name dealers)

Second table is user input of dealers transactions.(Table name is log) The first field of table two is the dealership name (field name is dealership).

Sometime not all dealers in the first table perform transactions.

How can show the dealers that are listed in the first table that are not listed in the second table.

I tried this, but it did not work

select dealers from dealers, log where dealers != dealership

How To Compare 2 Tables ?
I have two tables containing references and I would like to compute the differences but I have some problems. For example

table 1

+--------+
| ref |
+--------+
| A250 |
| B260 |
| G650 |
| D250 |
+--------+

table 2

+--------+
| ref |
+--------+
| A250 |
| B280 |
| Z650 |
| D250 |
| W650 |
+--------+

SELECT table1.`ref` FROM table1, table2 WHERE table1.`ref` NOT LIKE table2.`ref` group by table1.`ref` ;

does not compute corectly the diffÃ©rences.

On the other end :

SELECT table1.`ref` FROM table1, table2 WHERE table1.`ref` LIKE table2.`ref` group by table1.`ref` ;

computes correctly the comon references.

So what is wrong in the first query ? What can I do to see the differences between my 2 tables ?.

How Do I Compare Two Tables?
I have two tables with the same columns.Table A and Table B. I would like to compare a column in bot tables and determine if there is an entry in A but not in B, also I'd like to determin if there is an entry in B but not in A. How would I do this?

Compare Tables
I am looking to figure out a way to compare two tables and show me what is different.
One table is 'usertable'.This table contains 5 columns reference(key),tag,datatype,scantime,description. When I run my application,it takes a snapshoot of 'usertable' and create a temp table 'temptags'.Every couple of minutes I want to compare the two tables and determine what is different. Fist off,I want to compare the reference column of each table and determine if there is a new reference index or if any have been deleted.Then I want to look at all the other column and determin if any have changed.

These two tables are located in different databases 'usertable' is located in 'user' database and 'temptags' is located in 'tags' database.

Compare Two Tables
there is a way to compare fields and rows in two tables and display the values that differ from each other.

Example: I have a table called "products", and a table called "products_bak", with a few fields: id, name, quantity. "Products_bak" contains yesterdays values, and "products" contains todays updated values.

"Products_bak.quantity" contains yesterday's stock, and "products.quantity" contains current stock. Is there a way to compare the two tables and get the differences. I would like to be notified that the stock has increased from 3 to 5 in the first row, and that the name has changed from "TestOld" to "TestNew".

Compare Two Tables
How can I compare two tables and receive only the entry that are not the same as the first table.

Example:

First Table (fruit); contain apple, orange, lemon and lime. Second table (remove); orange, lime.
I need to have only apple and lemon as the result of my SQL SELECT.

Can't Compare 2 Tables' Columns
For some reason, comparing 2 tables using the operator "<>" is not working but using the opposite operator "=" is returning the correct opposite results.

I have 2 tables:
wp_posts with a column called 'post_title'
sas_products with a column called 'sas_product_id'

Both 'post_title' and 'sas_product_id' contain the same type of data, a product ID.

So if I do the opposite query from what I want, it works:

SELECT ID
FROM wp_posts, sas_products
WHERE post_title = sas_product_id
Results give me the 100 rows where the post_title and sas_product_id are equal.

But, if I do the following query, I get 10,000+ results when I should just get 2.

SELECT ID
FROM wp_posts, sas_products
WHERE post_title <> sas_product_id
Can someone help explain what it is I am missing here?

Compare Fields Tables
I would like to compare two tables, in both tables there is an user field called "user" (table_1.user, and table_2.user). Thee is however a small problem, in table_1 the users are inserted like this: 235463 and in table_2 the users where inserted like this: p235463!

Would it still be possible to compare this fields in some way (By using only the numbers maybe? But how?)

Compare 2 Tables ( <> Operation )
I have a small problem in comparing two tables, that has to return records that are not in the other table.

Suppose I have the following tables:

T1:               T2:
Field1            Field1
------            -------
1                  b
2                  a
d                  c
a                  1
x
z

And, this is the code:

select T1.Field1 from T1, T2
where T1.Field1 <> T2.Field1
group by T1.Field1

I was expecting the result of 2, d, x, z (which are not in T2). Instead, I got all 6 records from T1. I've tried other operation like !=, NOT IN, but came up the same. The Field1 on T1 is primary key.

Compare And Get Result From Two Tables
I'm trying to compare the id from the users table to the id of the white_player in the games table, and provide me with the users name. I think I am close, but unsure of how to actually get the result. Pretty sure the query is good, but not sure what the second line should look like.

$wplayer = mysql_fetch_object(mysql_query("SELECT ".$this->settings->mysql_prefix."users.nick," .$this->settings->mysql_prefix."users.id," .$this->settings->mysql_prefix."game.white_player from ".$this->settings->mysql_prefix."users ".$this->settings->mysql_prefix."game WHERE " .$this->settings->mysql_prefix."users.id = " .$this->settings->mysql_prefix."game.white_player "));
$wplayer = $wplayer->nick;

Compare Record In Two Tables
if there is an easy way to compare a row from table
a, to a row in table b with the same structure/field names and return
those fields which don't match?I have table_a storing the originally submitted applicant information,and table_b storing a version that can be changed. When a user makes a
change I'd like to compare it to the original table to see what fields
were changed, preferably without opening the row from both tables and
going field by field in code to see if they match...

How To Write Query To Compare Tables
I give in. I can't figure it out, and I know it's one of those things that once I see it I'll think, "OF COURSE!".

I have 2 tables, same DB.

Table 1 is named Policies
Table 2 is named Assignments

Both contain a PolicyID field.

I want to find out which PolicyID entries are ONLY in Policies, and not in Assignments.

So if "Select PolicyID from Assignments" returned the following:
1, 2, 3, 4, 5, 6

And "Select PolicyID from Policies" returned:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10

What do I use to return this? 7, 8, 9, 10

Any nudge, help, clue, etc. would be appreciated. I've looked thru examples of Outer Joins, Inner Joins, Lefts, Rights, Unions, Intersects, etc. and can't figure out which to use, all for what seems to be a simple operation!

Compare 2 Tables And Update One If A Match.
I have two tables "contact_tbl" and "address_tbl". I have a query that compares the email addresses between the two. If a match is found I need to update a specific field in "contact_tbl" with a value of 'yes'. My query is:

SELECT * FROM contact_tbl
INNER JOIN address_tbl
ON contact_tbl.email = address_tbl.email;

This works and I get a nice result set showing records where the email addresses match. I now need to update a field "status" in contact_tbl when there is a match. Have been tinkering with this for a while but not sure of the syntax and placement for the UPDATE code.

Compare Data Beetwen 2 Tables
MYSQL problem:

I'm trying to copy all unique data from a heap table
to a myisam table:
CREATE TABLE `heap_in` (
`id` varchar(32) NOT NULL default '',
`domain` varchar(50) NOT NULL default '',
) TYPE=HEAP;

CREATE TABLE `last_visits` (
`id` varchar(32) NOT NULL default '',
`domain` varchar(50) NOT NULL default '',
KEY `id` (`id`,`domain`)
) TYPE=MyISAM;

Heap table just record the data. heap_in.id and heap_in.domain are not unique.
While I'm inserting data to myisam table I want to insert just one time the same domain(if it is duplicated) for the same id. Code:

Insert Into Select + Delete?
Is there a way to copy some data from 1 table to another in a insert into...select statement AND remove the affected data from the original table?

ie combining these 2 statements:
CODEinsert into superCat_subCat (sCatId, subCatId) select from cat_subCat where catId = 327

delete from cat_subCat where catId = 327

Update / Insert / Delete Log
I'm wondering if there is any way to get a lot of recent update/insert/delete statements performed on a db by a particular user?

AUTO_INCREMENT, Delete And Then Insert...
I have a table of items:

CREATE TABLE item_table (
item_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
item_display tinyint(1) DEFAULT '1' NOT NULL,
item_parent MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL,
item_type tinyint(1) DEFAULT '0' NOT NULL,
PRIMARY KEY (item_id)
);

I also have an app that will allow me to update certain fields (item_display) for multiple items at one time.

Due to the fact that this update can occur on many, many items at one time, I do the following:

1. assemble a list of IDs (item_id) that are being updated
2. issue "DELETE WHERE item_id IN (list of IDs)"
3. issue "INSERT (item_id, item_display, etc) VALUES (values for item 1), (values for item 2) etc"

I am doing it this way as I do not wish to execute an UPDATE for each item being updated, as i believe it is faster?? and less resource intensive?? to execute one delete and one insert request for a bunch of records, as opposed to updating each one in turn.

My question is: It appears that the auto_increment is not being triggered during the mass insert (possibly because i am explicitly specifying the item_id??) and this is A GOOD thing, at least for my application's purposes.

Is this standard, documented MySQL behaviour? I hope so...

Also, am I correct in thinking that mulitple UPDATE requests would be slower/more intensive than one mass DELETE and one mass INSERT?

Compare 2 Tables And Find Rows Which Don't Match (was "Mysql Noob Question")
I have a products table and a products_description table. They both have products_id as primary key. However, products_description table has more products_id keys then the products table. How do i compare the 2 tables and show just the products_id that dont match from products_descripition table?

SELECT products.products_id, products_description.products_id FROM products, products_description WHERE products.products_id != products_description.products_id;

I tried that query but got some wierd results and also the results gave me 2 columns when I just want one.

Delete From All Tables Where...
Is it possible to write a query to delete all records, from all tables, where deleted='1'?

DELETE FROM myDB.all_tables
WHERE `deleted`='1'

Delete From Two Tables
I've got two tables in a mysql (4.0.0) database which are called 'gallery' and 'pictures'. Gallery simply holds the name and id of the galllery and pictures hold other information but each row has a gallery id (gid).

I need to delete a gallery from the gallery table, but at the same time, delete any rows from the pictures table that have that gallerys id.

I tried doing this:

DELETE In Various TABLES;
I don't use MySQL a lot, but I have a problem with DELETE.
I have a database "logging" with various TABLES.
Now I want to DELETE all the ROWS from all the TABLES that have an entry starting with "134.196.97." in the field IP.

Delete Duplicates From 2 Tables
MySQL
CREATE TABLE `subscribers` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`email` VARCHAR(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I have an accompanying table that houses additional subscriber data, with the column sd_sub_id being the subscribers table FK

MySQL
CREATE TABLE `subscribers_data` (
`sd_sub_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`sd_ip_address` VARCHAR(60) NOT NULL DEFAULT '',
PRIMARY KEY (`sd_sub_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Chances are that there won't be duplicate rows, but! I don't want to take that chance so I'm cooking up a 'remove duplicates' function. I have it set up to remove the duplicates from the subscribers table, but how do I get the corresponding records from the subscribers_data table and delete them as well?

MySQL
CREATE TEMPORARY TABLE subscribers_temp(id VARCHAR(10), email VARCHAR(60)) TYPE=HEAP;

INSERT INTO subscribers_temp(id,email) SELECT DISTINCT email FROM subscribers;

DELETE FROM subscribers;

INSERT INTO subscribers(id,email) SELECT id,email FROM subscribers_temp;

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?

Delete From Linked Tables
Is it possble to delete all records from a table and all records in a second table that are linked to that record?

MYSQL / PHP Delete From Two Tables
I need a query that will delete one vehicle(v_id) AND all service(s_id) records associated with the vehicle. The problem is that my existing query only works when there is one or more than one service record associated with the vehicle deleted...it will not work if there is one vehicle and no service records.

Here is my query:

DELETE vehicles, service FROM vehicles, service WHERE vehicles.v_id = ".$delete_vehicle." AND service.v_id = ".$delete_vehicle."

I am using PHP if that helps out with the solution.

I have two tables - the relationship is one vehicle(v_id) to none, one, or more than one service record(s_id):

service
------------
[s_id](index)
v_id
s_type
s_date
-----------
and
vehicles
------------
[v_id](index)
v_make
v_model

Delete From Multiple Tables
I am having trouble trying to figure out a delete query to work accross multiple tables. right now, i have something like this:

DELETE from ad_bill_info as b, ad_cc as c USING b,c where b.cc_id = c.id and b.id = 2

Delete Info From 4 Tables At Once But With Different Name For Key
I have four tables, let's say

tbl1tbl2tbl3user
-------------------------------
idididid
useriduseriduseridinfo
infoinfoinfo

Now how to delete everything of one user in a single query?

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>";
?>

DELETE From Mlutiple Tables
Sorry I did search the forum for this but didn't find much.
I tried reading the MySQL docs too but I didn't understand.

How can I do this in one query?

DELETE FROM trials WHERE id = $id
DELETE FROM trials_msg WHERE trial_id = $id
DELETE FROM trials_sites WHERE trial_id = $id
DELETE FROM trials_times WHERE trial_id = $id

Will it still work if there are rows to delete in one table but not in another?

Delete Tables With Prefix
How can i delete table have xxx_ prefix?

Delete Across Multiple Tables
I'm trying to help a friend with this. When I run this everything is fine

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

What am I doing wrong

Delete Rows From Multiple Tables
I am writing a simple PHP script to completely remove all references to a user from a whole bunch of tables (the "user" in this case is really a row of data identified by a user_id field within that row).

I tried to use "DELETE FROM * WHERE user_id = $whatever_it_is;" but clearly that would be too easy. What is the proper way to do this?

Delete Rows From Multiple Tables
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.

Delete Rows In Mutiple Tables
Using MySQL 4.0
I have 4 tables (T1, T2, T3, T4)

T1 - has the object that I would like to delete (across these tables) as the pk (known as object_pk)

T4 - will have one row of info that would correspond to T1's pk (with the field object_pk)
BOTH T2 & T3 has many rows would correspond to T1's pk (with the field object_pk)

I have been trying to create a delete command that would delete all references to T1's pk in all the tables listed above.

I was hoping that it was as easy as:
DELETE FROM T1, T2, T3, T4
WHERE object_pk = 1

But realized quickly that wasn't the way to go. Been trying various join statments but, being the noob that I am, surely have the syntax all messed up.

Comparing Two Tables And Delete Rows
I have two tables pluserdata and pluserlids. pluserdata contains all the users information such as ID username password email etc, and pluserlids contains just corresonding IDs (from pluserdata) and then leagueid which correspods to another table.

What I am after is a query to look at pluserlids and then delete any rows that do not have a corresonding id in pluserdata.

Another way of explaining it is that I have deleted about 1000 users (spam) from pluserdata but their ids are still in the pluserlids table so I want to check to see if they have been deleted then to remove their lid from pluserlids.

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!

How To Delete Fields From Different Tables With A Single Query
How to delete fields from different tables with a single query?

Complicate Delete-statement Involving Two Tables
I have two tables:
Persons and Relations

Persons contain personal data such as person_ID, name, adresse, etc.
and a special-status-code (a person with this code set = 1 is a VIP)

Relations contains a person_ID and a vip_ID (the vip_ID is in fact a
person_ID - it is just a person with his special-status-code = 1). So
the relation table is used to register who a VIP knows (his contacts).

My problem is when I delete a VIP.

I have no problems just deleting the VIP from the Persons-table, but I
also need to delete all of his contacts (not only from the
Relations-table but also from the Persons-table).

I think this might be part of the solution:
ALTER PROCEDURE Delete_Vip
(
@Vip_ID INT /* ID of the VIP to be deleted */
)

AS

DELETE
FROM Persons p
WHERE r.Vip_ID = @Vip_ID
AND p.Person_ID NOT IN (
SELECT * FROM Relations r WHERE Vip_ID <> @Vip_ID
)

This does not work, but the idea in the subquery is to NOT delete
persons that are known by other VIP's than the one I am deleting. In
other words I am only interested in the VIP's exclusive contacts.

I am also quite sure that the above SQL-statement does not remove the
VIP's posts in the Relations-table.

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.

Insert Tables
I am using mysql through the contol panel on windows server. basically trying to build a dynamic website. In the control panel, i can see the menus to create DSN and database.

Can anyone tell me where do i create the tables there is no site builder available, so should i create externally and then upload through ftp.

Insert From 2 Tables
I want to insert into a table field1, field2, field3, from table_one, and I want to add onto the end of field3 from the same field-name in table_two.

Insert into table_one(field1, field2, field3)
Select field1, field2, field3
From table_one

â€¦ and then take field3 from table_two and add it onto the end of the text entered from field3 from table_one.

Is this possible.

INSERT Into Two Tables
How do I insert data into two tables at the same time?

My DB structure is as follows:
CREATE TABLE `speakers`(
`speaker_id` INT(9) NOT NULL auto_increment,
`speaker` VARCHAR(30) NOT NULL,
PRIMARY KEY (speaker_id);
)

CREATE TABLE `RSS_info`(
`cast_id` INT(9) NOT NULL auto_increment,
`speaker_id` INT(9) NOT NULL,
`title` VARCHAR(50) NOT NULL,
`filename` VARCHAR(20) NOT NULL,
`description` VARCHAR(200) NOT NULL,
`pubDate` VARCHAR(32) NOT NULL,
`size` INT(20) NOT NULL,
PRIMARY KEY (cast_id),
FOREIGN KEY (speaker_id) REFERENCES `speakers` (speaker_id) on delete cascade
); TYPE=MYISAM

My INSERT statement for one table is below.

INSERT INTO `speakers` VALUES (NULL, 'Speaker Name');

What I need to be able to do is insert the rest of the data that I'll be receiving into the second table.

2 Tables And An INSERT
im trying to copy a row from one table to the other, but 1 of the table's has an extra AUTO_INCREMENT column.

INSERT INTO fixtures_h values (NULL, SELECT * FROM fixtures WHERE No = 1).

Insert In 2 Tables
I have two tables message, contacts, sch_contacts

columns for table message: messageid,description
columns for table contacts: contactid,email
columns for table sch_contacts: messageid,contactid

Now, i have to insert one row from table message and all the rows from table contacts in to table sch_contacts.

insert into sch_contacts(1,(select contactid from contacts))
for ease 1 can be treated as messageid which user is going to input.
I am getting error on sql which says subquery return more than 1 row
how to insert all the rows from 1 table into another?

2 Tables INSERT
I have two tables, and both have an id field. the id field in the first table is auto_increment, so when i insert data, i leave this field out. the problem is, i need to insert the same id number into my other table, but i cannot use auto_increment in the id field in the second table, because there are more than one occurence of each number.