deleting rows with composite primary key
in [Databases]
|
From: "Systems" on 18 May 2010 13:14 Is there a different syntax to the mysql delete statement when the "WHERE" clause points only to half of the primary key? The structure is as follows: CREATE TABLE IF NOT EXISTS ` table1` ( `id1` int(10) unsigned NOT NULL, `id2` int(10) unsigned NOT NULL, PRIMARY KEY (`id1`,`id2`), KEY `id2` (`id2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Query is $query = "DELETE * FROM table1 WHERE id1 = '$recID';"; Error is a 1064 syntax error. Any help is appreciated. Eli
From: Niel Archer on 18 May 2010 14:49 > Is there a different syntax to the mysql delete statement when the "WHERE" > clause points only to half of the primary key? > > The structure is as follows: > CREATE TABLE IF NOT EXISTS ` table1` ( > `id1` int(10) unsigned NOT NULL, > `id2` int(10) unsigned NOT NULL, > PRIMARY KEY (`id1`,`id2`), > KEY `id2` (`id2`) > ) ENGINE=MyISAM DEFAULT CHARSET=utf8; > > Query is > $query = "DELETE * FROM table1 WHERE id1 = '$recID';"; Integer values do not need to be quoted. You should be able to drop the semi-colon from the query too. See if that helps > Error is a 1064 syntax error. > > Any help is appreciated. > > Eli > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- Niel Archer niel.archer (at) blueyonder.co.uk
From: Niel Archer on 18 May 2010 14:51 > > Is there a different syntax to the mysql delete statement when the "WHERE" > > clause points only to half of the primary key? > > > > The structure is as follows: > > CREATE TABLE IF NOT EXISTS ` table1` ( > > `id1` int(10) unsigned NOT NULL, > > `id2` int(10) unsigned NOT NULL, > > PRIMARY KEY (`id1`,`id2`), > > KEY `id2` (`id2`) > > ) ENGINE=MyISAM DEFAULT CHARSET=utf8; > > > > Query is > > $query = "DELETE * FROM table1 WHERE id1 = '$recID';"; > > Integer values do not need to be quoted. You should be able to drop the > semi-colon from the query too. See if that helps > > > Error is a 1064 syntax error. And try var_dump()ing the complete query to make sure it looks as you would expect. > > Any help is appreciated. > > > > Eli > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > -- > Niel Archer > niel.archer (at) blueyonder.co.uk > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- Niel Archer niel.archer (at) blueyonder.co.uk
From: Bastien Koert on 18 May 2010 15:42 On Tue, May 18, 2010 at 2:51 PM, Niel Archer <not(a)chance.now> wrote: >> > Is there a different syntax to the mysql delete statement when the "WHERE" >> > clause points only to half of the primary key? >> > >> > The structure is as follows: >> > CREATE TABLE IF NOT EXISTS ` table1` ( >> > `id1` int(10) unsigned NOT NULL, >> > `id2` int(10) unsigned NOT NULL, >> > PRIMARY KEY (`id1`,`id2`), >> > KEY `id2` (`id2`) >> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8; >> > >> > Query is >> > $query = "DELETE * FROM table1 WHERE id1 = '$recID';"; >> >> Integer values do not need to be quoted. You should be able to drop the >> semi-colon from the query too. See if that helps >> >> > Error is a 1064 syntax error. > > And try var_dump()ing the complete query to make sure it looks as you > would expect. > > >> > Any help is appreciated. >> > >> > Eli >> > >> > >> > -- >> > PHP Database Mailing List (http://www.php.net/) >> > To unsubscribe, visit: http://www.php.net/unsub.php >> >> -- >> Niel Archer >> niel.archer (at) blueyonder.co.uk >> >> >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php > > -- > Niel Archer > niel.archer (at) blueyonder.co.uk > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > The syntax should be "DELETE FROM table1 WHERE id1 = '$recID';"; No need for the * -- Bastien Cat, the other other white meat
From: Niel Archer on 18 May 2010 15:54 > On Tue, May 18, 2010 at 2:51 PM, Niel Archer <not(a)chance.now> wrote: > >> > Is there a different syntax to the mysql delete statement when the "WHERE" > >> > clause points only to half of the primary key? > >> > > >> > The structure is as follows: > >> > CREATE TABLE IF NOT EXISTS ` table1` ( > >> > `id1` int(10) unsigned NOT NULL, > >> > `id2` int(10) unsigned NOT NULL, > >> > PRIMARY KEY (`id1`,`id2`), > >> > KEY `id2` (`id2`) > >> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8; > >> > > >> > Query is > >> > $query = "DELETE * FROM table1 WHERE id1 = '$recID';"; > >> > >> Integer values do not need to be quoted. You should be able to drop the > >> semi-colon from the query too. See if that helps > >> > >> > Error is a 1064 syntax error. > > > > And try var_dump()ing the complete query to make sure it looks as you > > would expect. > > > > > >> > Any help is appreciated. > >> > > >> > Eli > >> > > >> > > >> > -- > >> > PHP Database Mailing List (http://www.php.net/) > >> > To unsubscribe, visit: http://www.php.net/unsub.php > >> > >> -- > >> Niel Archer > >> niel.archer (at) blueyonder.co.uk > >> > >> > >> > >> -- > >> PHP Database Mailing List (http://www.php.net/) > >> To unsubscribe, visit: http://www.php.net/unsub.php > > > > -- > > Niel Archer > > niel.archer (at) blueyonder.co.uk > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > The syntax should be > > "DELETE FROM table1 WHERE id1 = '$recID';"; > > No need for the * DOH! How did I miss that? Must be time for bed! > -- > > Bastien > > Cat, the other other white meat > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- Niel Archer niel.archer (at) blueyonder.co.uk
|
Pages: 1 Prev: MySQL: Creating a database with Next: Need Simple PHP mysql search engine script |