[SOLVED] Delete from two tables - Please Help.

[l!m!t]

Hello,

 

I have been trying to figure this out for hours , yet I cant seem to get it to work.

 

I have two separate tables - ADDRESS and CUSTOMER I want to delete all the ROWS under the table ADDRESS, WHERE CUSTOMER CUSTOMER_ID=1

 

(CUSTOMER_ID is located in the CUSTOMERS table.) I tried inner Joins, left joins, but cant seem to figure it out.

 

any help would be great.

[tinker]

I've not figured it out yet but supposedly these forms of joins are the one's to use when deleting, http://dev.mysql.com/doc/refman/5.1/en/join.html

 

oh no, I might of done it...

 

$host = 'localhost';
$user = 'user';
$pass = 'pass';
$db = 'db';

$conn = mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db, $conn) or die(mysql_error());

/*
$s = "DROP TABLE test_cust";
mysql_query($s, $conn);

$s = "DROP TABLE test_addr";
mysql_query($s, $conn);
*/

$s = "CREATE TABLE test_cust (id int not null primary key auto_increment, name varchar(32), pass varchar(32) )";
if(mysql_query($s, $conn))
{
print "Created table<br>";

$s = "INSERT INTO test_cust VALUES ('', 'me', 'password'), ('', 'you', 'password') ";
$res = mysql_query($s, $conn) or die(mysql_error());
}
else
{
print "Table creation failed<br>";
}

$s = "CREATE TABLE test_addr (id int not null primary key auto_increment, cust_id int not null, street varchar(32), city varchar(32) )";
if(mysql_query($s, $conn))
{
print "Created table<br>";

$s = "INSERT INTO test_addr VALUES ('', 1, 'here', 'there'), ('', 2, 'near', 'far') ";
$res = mysql_query($s, $conn) or die(mysql_error());
}
else
{
print "Table creation failed<br>";
}

print "<br>";
print "<br>";

$s = "SELECT * FROM test_cust LEFT JOIN test_addr on test_cust.id = cust_id";
$res = mysql_query($s, $conn) or die(mysql_error());
while ($a = mysql_fetch_array($res))
{
print $a['id'].": ".$a['name']." - ".$a['pass']." - ".$a['street']." - ".$a['city']."<br>";
}
print "<br>";

$s = "DELETE FROM test_addr USING test_addr LEFT JOIN test_cust on cust_id = test_cust.id WHERE name = 'me' ";
$res = mysql_query($s, $conn) or die(mysql_error());
print "<br>";

$s = "SELECT * FROM test_cust LEFT JOIN test_addr on test_cust.id = cust_id";
$res = mysql_query($s, $conn) or die(mysql_error());
while ($a = mysql_fetch_array($res))
{
print $a['id'].": ".$a['name']." - ".$a['pass']." - ".$a['street']." - ".$a['city']."<br>";
}

 

 

Found in 'Posted by wendy reetz on January 8 2005 8:06pm' post here...

[toplay]

This query assumes there's a customer_id column in both tables and that's how they're tied together.

 

DELETE FROM `ADDRESS` a

JOIN `CUSTOMER` c ON c.`CUSTOMER_ID` = a.`CUSTOMER_ID`

WHERE a.`CUSTOMER_ID` = 1

;

 

 

[l!m!t]

This query assumes there's a customer_id column in both tables and that's how they're tied together.

 

DELETE FROM `ADDRESS` a

JOIN `CUSTOMER` c ON c.`CUSTOMER_ID` = a.`CUSTOMER_ID`

WHERE a.`CUSTOMER_ID` = 1

;

 

 

 

Thanks everyone for the help!  I am still a little stuck, the query is  working , but it seems to be doing the opposite of what I need. I am querying it first to see the results and instead of displaying only the "customer_default_id"(s) that are NOT associated with the address_book_id, its displaying  id's that ARE associated.

 

I tried sticking an !  in front of the = , but doesn't seem to work. Any ideas would be great.

 

query ("select * FROM `address_book` a
JOIN `customers` c ON c.`customers_default_address_id` WHERE c.`customers_default_address_id`=a.address_book_id
");

 

Any ideas on how to only display results where the "customers_default_address_id" doesn't match the  "address_book_id"

 

 

 

 

 

[revraz]

Show the two table layouts with some sample data.

[toplay]

This "NOT associated" is completely the opposite of what you originally asked for.

 

One of these will probably be what you want:

 

# Returns all address book rows where there's no match in customers table

SELECT *

FROM `address_book` a

LEFT JOIN `customers` c ON c.`customers_default_address_id` = a.`address_book_id`

WHERE c.`customers_default_address_id` IS NULL

 

# Returns all customer rows where there's no match in address book table

SELECT *

FROM `customers` c

LEFT JOIN `address_book` a ON a.`address_book_id` = c.`customers_default_address_id`

WHERE a.`address_book_id` IS NULL

 

 

You can use the same for deleting.

 

[l!m!t]

Show the two table layouts with some sample data.

 

I will try to explain I am really new at this so will do my best.

 

customer_default_address_id and address_book_id are the same IDs only in different tables.

 

When I run this query

 

$foo=query ("select * FROM `address_book` a
JOIN `customers` c  WHERE  c.customers_default_address_id = a.address_book_id"); 


while ($a = mysql_fetch_array($foo))
{
echo $a['address_book_id'];
}

 

I get 26 and 3 which are the ID's of the customers_default_address_id  . The query works and is correct, yet I need it to get the ID's that do not macth the customers_default_address_id

(in this case it would be 27)

 

I am trying to get all the ids from ADDRESS_BOOK=>"address_book_id" that are not in the CUSTOMERS => customers_default_address_id .

 

I tried sticking an ! mark in front , but then it displays all ID's (example - c.customers_default_address_id != a.address_book_id  .... displays 3,26,27)

 

The whole purpose of this is to delete roughly 85,000 address_books entry's unless the address_book_id = the customers_default_address_id..

 

Any help wold be great .

 

Thanks again.

[l!m!t]

This "NOT associated" is completely the opposite of what you originally asked for.

 

One of these will probably be what you want:

 

# Returns all address book rows where there's no match in customers table

SELECT *

FROM `address_book` a

LEFT JOIN `customers` c ON c.`customers_default_address_id` = a.`address_book_id`

WHERE c.`customers_default_address_id` IS NULL

 

# Returns all customer rows where there's no match in address book table

SELECT *

FROM `customers` c

LEFT JOIN `address_book` a ON a.`address_book_id` = c.`customers_default_address_id`

WHERE a.`address_book_id` IS NULL

 

 

You can use the same for deleting.

 

 

 

  Thank you , Thank you!!!! Works Great. So to delete I just change :select" to delete?

[toplay]

Yes. Replace the "SELECT *" with "DELETE". I would recommend you back up your tables first (just in case).

 

[l!m!t]

Yes. Replace the "SELECT *" with "DELETE". I would recommend you back up your tables first (just in case).

 

 

I just did DELETE a.* and it deletes all address_book entry's where customers_default_address_id is NULL.

 

Here the DELETE query for anyone else who may have similar troubles.

 

$query = db ("DELETE a.* FROM `address_book` a LEFT JOIN `customers` c ON c.`customers_default_address_id` = a.`address_book_id` WHERE c.`customers_default_address_id` IS NULL"); 

 

Thanks again for the help greatly appreciated !!   

 

 

[toplay]

Remove the a.* before the FROM

 

http://dev.mysql.com/doc/refman/5.0/en/delete.html