Whats wrong with this SQL query..

[billy_111]

Hi,

 

I'm trying to use the following delete query:

 

DELETE FROM People LEFT JOIN PeopleCon ON People.Pid = PeopleCon.Person_id WHERE People.Pname = 'Peter Kay','Jake Welsh' 

 

Reason for doing it like this is i want to delete from 2 different tables which can be joined by an ID, table structure is below:

 

CREATE TABLE IF NOT EXISTS `People` (

`Pid` int(11) NOT NULL AUTO_INCREMENT,

`Pname` varchar(255) NOT NULL,

`Pdateadded` datetime NOT NULL,

`Pdeleted` int(1) NOT NULL,

PRIMARY KEY (`Pid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

 

 

CREATE TABLE IF NOT EXISTS `PeopleCon` (

`PCid` int(5) NOT NULL AUTO_INCREMENT,

`Person_id` int(5) NOT NULL,

`PCHid` int(5) NOT NULL,

`Pid` int(5) NOT NULL,

`PCorder` int(2) NOT NULL,

`PCdateadded` datetime NOT NULL,

`PCdeleted` int(1) NOT NULL,

PRIMARY KEY (`PCid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

 

I even tried doing it like this after a quick google search:

 

DELETE t1, t2 FROM People AS t1 LEFT JOIN PeopleCon AS t2 ON t1.Pid = t2.Person_id WHERE t1.Pname = 'Peter Kay','Jake Welsh'; 

 

But had no luck.

 

Any ideas?

 

Thanks

[AbraCadaver]

Are you trying to delete from both tables or just one? Regardless, this isn't proper:

 

WHERE t1.Pname = 'Peter Kay','Jake Welsh'

 

It needs to be:

 

WHERE t1.Pname IN('Peter Kay','Jake Welsh')

 

or:

 

WHERE t1.Pname = 'Peter Kay' OR t1.Pname =  'Jake Welsh'

 

[bh]

DELETE People, PeopleCon FROM People LEFT JOIN PeopleCon ON People.Pid = PeopleCon.Person_id WHERE People.Pname = 'Peter Kay' OR People.Pname = 'Jake Welsh'; 

 

?

[mikosiko]

If the two table have a clear relationship as in your case, I rather prefer to implement/enforce the Referential Integrity at DataBase level with a FOREIGN KEY and an ON DELETE CASCADE. In that way you only be deleting from the "Master" table PEOPLE... the ON DELETE CASCADE will take care of automatically delete from the PeopleCon Table.