MYSQL :: Compare 2 Tables And Delete

Compare And Delete Or Insert Within Tables
I have a CSV file that contains addresses. I need to compare the csv file to a MySQL table (tbl_bl).

Then if the addresses are not found in tbl_bl, I need to add the addresses to another table (tbl_wl) Only if they do not already exist in the tbl_wl table. I can create simple SQL queries but I'm not so sure about something this complex.

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:

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.

ON DELETE CASCADE ON DELETE UPDATE
I'm using this "reference_definition" with the motor MyISAM and version 5.0.27-community-nt, and it doesn't work.

The child tables don't delete (with "ON DELETE CASCADE") or doesn't update (with "ON UPDATE CASCADE")

Compare Row 1 With Row 2
I have a table with data that is supposed to only increase in value, but due to bugs outside my control, I need to check the entries in the database on a periodic basis.

I can of course do this in a scripting language like php but I was wondering if there is a way to do it in MySQL with some kind of SELECT query reducing the server load?

the table looks like:
id auto_increment
timestamp
data (numeric value that will increase with time)

What I'm looking for is something like this
SELECT id FROM table WHERE data is less than previous row's data ORDER BY timestamp

Compare
How can I stop a function in PHP?

When comparing data I want to stop the function on a hit.

Function compare($data) {

For ($n=1; $n<=10000; $n++) {

For ($m=1; $m<=10000; $m++) {

If $data[$n]==data[$m] {

How To Compare Different Datatypes?
how to create user except root account ?

Compare Dates
Using mysql 4.0, which doesn't support DATEDIFF

I'd like to compare two dates and check if the difference is 6 weeks or greater. How can I do this?

Compare 2 Databases
I have 2 CMS Systems running on MySql
I was upgrading one of them.
During the update(s), i messed some fields-defs at some tables.

Is there any possibilities to compare those two databases to see, which Field-Attributes are different (Just Name, Type) and which Tables are different (has table1 on DB1 other fields than table1 on DB2)

Compare Strings In SQL
I have a string field where are stored dates and i want to select from this table like this:

"SELECT * FROM [table] WHERE field_date>='date1' AND field_date<='date2'".

Apparently doens't work, the query returning all the rows in the table. Is there a posibility for this string comparison or i should probably convert the field that stores these dates into the date one?

Compare Schema
I have two MySQL database. How can I compare the schema. I wan't to see if there are any differences in them. Is there a tool for this?

Date Compare
I need to compare dates without the time. In oracle, I would use a trunc() function to drop the time. How is this done in MySQL?

LIKE Compare Two Columns
I have a issue comparing two columns.

I am using a select query, and here it is:

SELECT project_name AS project, project_manager AS manager, percent_complete AS completed, plcp_phase AS phase, (

SELECT COUNT( * ) FROM meetings WHERE project_name = project) AS meetings,

(SELECT COUNT( * ) FROM filesdocs WHERE product LIKE project) AS files,

(SELECT COUNT( * ) FROM issues WHERE project_name = project) AS issues

FROM projects GROUP BY project");

I have several tables, projects, whehere i obtain the project name, filesdocs, where i am looking for the amount of files for each project, and the same with issues and meetings.

In filesdocs, product name could be "xxx", or "yyy", or any other project name, OR "xxx yyy" (with a space), becuase that file is relavant for TWO projects. Which would mean if i LIKE seach for "%xxx%", then the files with the products named "xxx" AND "xxx yyy" will get returned. Works great if i supply the project name as variable, since i can quote the variable as "%variable%".

However, in the search above where i the seach finds all the different projects in one search, how can i do a LIKE seach with %'s on the column alias 'project'? Although the alias "project" LIKE product works, it does not return the count of files where products is in the form of "xxx yyy", as obviously if the current project in the sql search = "xxx", then "xxx" obviously does NOT equal "xxx yyy" and the count misses a file. But i cannot find a way of using the alias with % % marks encapsulating it. Code: