deleting rows with composite primary key

am 18.05.2010 19:14:44 von 3dgtech

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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: deleting rows with composite primary key

am 18.05.2010 20:49:38 von Niel Archer

> 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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: deleting rows with composite primary key

am 18.05.2010 20:51:44 von Niel Archer

> > 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

Re: deleting rows with composite primary key

am 18.05.2010 21:42:33 von Phpster

On Tue, May 18, 2010 at 2:51 PM, Niel Archer wrote:
>> > Is there a different syntax to the mysql delete statement when the "WH=
ERE"
>> > clause points only to half of the primary key?
>> >
>> > The structure is as follows:
>> > CREATE TABLE IF NOT EXISTS ` table1` (
>> > =A0 `id1` int(10) unsigned NOT NULL,
>> > =A0 `id2` int(10) unsigned NOT NULL,
>> > =A0 PRIMARY KEY =A0(`id1`,`id2`),
>> > =A0 KEY `id2` (`id2`)
>> > ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8;
>> >
>> > Query is
>> > $query =3D "DELETE * FROM table1 WHERE id1 =3D '$recID';";
>>
>> Integer values do not need to be quoted. You should be able to drop the
>> semi-colon from the query too. =A0See 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 =3D '$recID';";

No need for the *

--=20

Bastien

Cat, the other other white meat

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: deleting rows with composite primary key

am 18.05.2010 21:54:13 von Niel Archer

> On Tue, May 18, 2010 at 2:51 PM, Niel Archer 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` (
> >> > =A0 `id1` int(10) unsigned NOT NULL,
> >> > =A0 `id2` int(10) unsigned NOT NULL,
> >> > =A0 PRIMARY KEY =A0(`id1`,`id2`),
> >> > =A0 KEY `id2` (`id2`)
> >> > ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8;
> >> >
> >> > Query is
> >> > $query =3D "DELETE * FROM table1 WHERE id1 =3D '$recID';";
> >>
> >> Integer values do not need to be quoted. You should be able to drop th=
e
> >> semi-colon from the query too. =A0See 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
> >
> >
>=20
> The syntax should be
>=20
> "DELETE FROM table1 WHERE id1 =3D '$recID';";
>=20
> No need for the *

DOH! How did I miss that? Must be time for bed!

> --=20
>=20
> Bastien
>=20
> Cat, the other other white meat
>=20
> --=20
> 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