CODEWITHSUNDEEP
phpsqldelete-rowcorresponding-records
user2142549
user2142549

Reputation: 11

How to delete one row that has a duplicate username to other rows?

This is the table:

username:     id:

john          1
john          2
john          56
john          75
john          98

Now I want query and delete one of these rows randomly and keep the other four- assuming I do not know the value of "ID" because it was Auto-Incremented.

Here's the code, I'm not sure what to add so it only deletes one of the rows associated with 'john'

$query = ("DELETE FROM table WHERE username='$name' && id=''");

How do I modify this so it deletes one row with the name john and not all of them?

Fixed Code

Simple solution that worked.

$query = ("DELETE FROM table WHERE username='$name' LIMIT 1");

Upvotes: 1

Views: 454

Answers (2)

advermark
advermark

Reputation: 231

I'm not sure if its possible, but perhaps LIMIT the result to 1?

$query = ("DELETE FROM table WHERE username='$name' LIMIT 1");

Upvotes: 0

shrimpwagon
shrimpwagon

Reputation: 905

I believe this will work:

$name = 'john';

$query = ("DELETE FROM table WHERE id = (SELECT id FROM table WHERE username = '$name' ORDER BY RAND() LIMIT 1)");

Upvotes: 0

Related Questions