CODEWITHSUNDEEP
phpmysql
itsfawwaz
itsfawwaz

Reputation: 117

deleting data from two tables

i'm trying to delete data from two tables, the first table(maklumatakaun) has "id" as its primary key and 2nd table(detailakaun) has id as its foreign key. right now my query lead to an error #1064

$idURL = $_GET['id'];

$query = "DELETE FROM detailakaun
          INNER JOIN maklumatakaun 
          WHERE maklumatakaun.id = detailakaun.id
          AND id = '$idURL'";

i echo the data id with this code

<a href="buang_akaun.php?id=<?php echo $id;?>

does anybody know how to do this?

EDIT: THE ERROR MESSAGE

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN maklumatakaun WHERE maklumatakaun.id = detailakaun.id AND id = '53'' at line 1

EDIT NEW ERROR detailakaun has its own A.I id which is idDetail, and inside table detailakaun there are column NoTelefon , KodLokasi and KodJenisAkaun which can have more than one under one identical maklumatakaun.id , now my problem is i can't delete if there's more than one

Upvotes: 0

Views: 157

Answers (3)

Ataboy Josef
Ataboy Josef

Reputation: 2101

No need to go through any join condition if there are keys as you specified.

$query = "DELETE FROM maklumatakaun 
          WHERE id = '$idURL'";

This will remove records from both table. But if there's a key exist as you specified, then you can not delete the record from foreign key table unles you remove the relation between two tables.

Upvotes: 0

Colum
Colum

Reputation: 986

You could set up a trigger, so that when you delete a record from one table it deletes the corresponding record from the second table. Have a look here : http://code.tutsplus.com/articles/introduction-to-mysql-triggers--net-12226

The advantages are that you only need to execute one query, the downside being there may possibly be a time you want to delete a record from the first table but leave the second table intact

Upvotes: 0

juergen d
juergen d

Reputation: 204784

If you join you need an ON clause and when joining in a delete then you need to specify from which table you delete

DELETE m, d
FROM detailakaun d
INNER JOIN maklumatakaun m ON m.id = d.id
WHERE d.id = '$idURL'

Upvotes: 1

Related Questions