Jump to content


Photo

Delete from multiple tables


  • Please log in to reply
6 replies to this topic

#1 maxat

maxat
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 21 July 2006 - 08:52 AM

Hi, I am new in mysql and having problem with deleting rows from multiple tables.

I have 3 tables

1. table users
us_id <- primary key

2. table items

  od_id <- primary key
  pd_id <- primary key
  us_id <- primary key
  it_qty

3.table orders

od_id <- primary key


In table "items" all are foreign keys from another tables except it_qty.
The problem is when I delete user from table "user", I want delete all his records from "items" and "order" tables. My tables are in MyIsam. so far I couldn't get any valid query.
Please help me with this query. Thanks in advance.

#2 designationlocutus

designationlocutus
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts

Posted 21 July 2006 - 09:18 AM

Are you linking all of your tables together within the DELETE query?

Please post your query contruct.

#3 maxat

maxat
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 21 July 2006 - 09:23 AM

DELETE FROM users, items, orders WHERE users.us_id = items.us_id AND items.od_id = orders.od_id AND users.us_id = '$usrID'

is there anything wrong?

#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 21 July 2006 - 01:51 PM

Try the following
DELETE
u,i,o
FROM
users AS u
LEFT JOIN
items AS i
ON u.us_id=i.us_id
LEFT JOIN
orders AS o
ON i.od_id=o.od_id
WHERE u.us_id = $usrID

Delete Syntax

#5 maxat

maxat
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 21 July 2006 - 02:07 PM

Thanks shoz for reply.

I have tried to run the query, but getting an error " Not unique table/alias: 'u' " Do I have to change my column names?

#6 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 21 July 2006 - 02:21 PM

MYSQL 4.0 and MYSQL 4.1 or higher deal with aliases in multi-table deletes differently. To avoid problems try this query which doesn't use aliases
DELETE
users,items,orders
FROM
users
LEFT JOIN
items
ON users.us_id=items.us_id
LEFT JOIN
orders
ON items.od_id=orders.od_id
WHERE users.us_id = $usrID


#7 maxat

maxat
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 21 July 2006 - 02:57 PM

Thank you shoz very much. It is working now.  I realy appreciate your help.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users