1

I have 2 tables:

"customers" and "addresses". A customer can have several addresses, so they have an "n:m" relationship. For this reason, I also have the table "customer-addr".

This is how my tables look like:

                    +---------------+       
+-----------+       | customer_addr |       
| customers |       +---------------+       +-----------+
+-----------+       | id            |       | addresses |
| id        | <---> | cid           |       +-----------+
| name      |       | aid           | <---> | id        |
+-----------+       +---------------+       | address   |
                                            +-----------+

I need to update all customer-data incl. all addresses. For this reason I thought about deleting all existing addresses first, then updating the customer-table, and after that, I create every address new.

My question: How can I delete all existing addresses from one customer efficiently? (I have to remove rows from 2 tables).

Is there a single-statement I can use? (Without the cascade-method, this is too risky)

Or can I do it with 2 statements, without using subselects?

What's the best approach for this?

Notice that I'm using postgresql

Edit:

My whole database-design is more complex, and the address-table is not only a child from "customers" but also from "suppliers","bulkbuyers",..

Every address belongs to only one customer OR one supplier OR one bulkbuyer. (No address is used by more than one parent / no address-sharing)

Ever customer/supplier/.. can have multiple addresses.

For this reason, the edited solution from zebediah49 won't work, because it would also delete all addresses from every supplier/bulkbuyer/...

Improve this question
7
  • If it's all data, can't you just drop the entire content of customer_addr and addresses ? EDIT:OK, you want for a single customer. Edit fixed my question; thanks. Commented Aug 1, 2013 at 16:13
  • I only want to delete the data for one specific customer. And I only know the customer's id Commented Aug 1, 2013 at 16:14
  • Can address belong to 2+ customers? Commented Aug 1, 2013 at 16:21
  • How do you expect your data to differ after you do all this? Commented Aug 1, 2013 at 16:22
  • @Dan Bracuk: After every operation, the following changes could have happened: (1) customer-Data (eg. other customer-name) (2) address-Data (eg. other street) (3) new addresses added (4) old address removed (5) a combination of 1-4. So after this operation, everything customer-related could have changed Commented Aug 1, 2013 at 16:26

2 Answers 2

Reset to default
2

I would use a writable CTE also called data-modifying CTE in PostgreSQL 9.1 or later:

WITH del AS (
   DELETE FROM customer_addr
   WHERE  cid = $kill_this_cid
   RETURNING aid
   )
DELETE FROM addresses a
USING  (SELECT DISTINCT aid FROM del) d
WHERE  a.id = d.aid;

This should be fastest and safest.

If (cid, aid) is defined UNIQUE in customer_addr you don't need the DISTINCT step:

...
DELETE FROM addresses a
USING  del d
WHERE  a.id = d.aid;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Wow, I have no idea how this is working, but it does exactly what I want. I'll do some research how it works, thanks for the fast answer
@maja: Your research can be as simple as following the link to the manual I included. ;)
@maja: Concerning your rejected edit: The subquery is required to have an alias. But I applied your fix for the column name id to fit the question, even if I despise that. Why?
You're right, didn't think of that. I edited it to reach the minimum of 6 character-changes per edit. But the opening-bracket after "AS" is still missing
1

EDIT:

Got it; this is safer because of the risk of two customers sharing an address anyway:

DELETE FROM customer_addr WHERE cid = $TARGET_CID;
DELETE FROM addresses WHERE id NOT IN (SELECT aid FROM customer_addr);

First, delete all references, then delete all unreferenced addresses. Note that you could, for example, only do the first step, and run the "cleanup" second step at a later time.

I would suggest a two step transaction:

DELETE FROM addresses WHERE id IN (SELECT ca.aid FROM customers c LEFT JOIN customer_addr ca ON ca.cid=c.id WHERE c.name='$NAME_TO_DELETE');
DELETE FROM customer_addr WHERE cid = (SELECT id FROM customers WHERE name='$NAME_TO_DELETE');

If you have the customer ID already (EDIT: You do), you can skip most of that:

DELETE FROM addresses WHERE id IN (SELECT aid FROM customer_addr WHERE cid=$TARGET_CID);
DELETE FROM customer_addr WHERE cid = $TARGET_CID;

Wrap those with the appropriate transactional BEGIN/END, to make sure that you don't end up in an inconsistent state, and you should be set.

Improve this answer

4 Comments

This won't work, because I can't delete data from addresses, as long as the corresponding rows in customer_addr exists. The customer_addr -entries are still pointing to the addresses (foreign-key constraint)
It won't work even if it's done within a single transaction? Interesting; I'll see if I can come up with an alternative.
Unfortunatelly this won't work either, see my edited question :(
You could always use a NOT IN (... UNION ... ) but that leaves far more space for mistakes, and further updating later if you add a new dependent table. Given the updated information, the other answer is a better method. Alternatively (don't actually do this), you can trust your foreign keys, and just DELETE FROM addresses, with every referenced address throwing an error instead of getting deleted -- you'll also need to make sure it keeps going instead of quitting on the first invalid delete.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.