delete complicated value from mysql database

Question

My table structure in the database;

Table "System_t"

id         |    Owner     |    System
===========================================
1          |    Joe       |    Registration
           |              |    Event             //contains new line
           |              |    Aims
-------------------------------------------
2          |    Rose      |    Ticket
           |              |    Elearning

NOTE : The data field "System" were saved from a Textarea box.

My problem is how am I going to delete only the "Event" data from the field "System"?

My table should be look like this when it is deleted;

id         |    Owner     |    System
===========================================
1          |    Joe       |    Registration
           |              |    Aims
-------------------------------------------
2          |    Rose      |    Ticket
           |              |    Elearning

Any idea how to do it without changing the table structure?

Does the `System` column always contain the text `Event` or is it a different event name in each cell? — bendataclear, Jun 15 '12 at 09:27
Then if it's a different text in each cell, he'll surely manage it with PHP, not MYSQL. — BMN, Jun 15 '12 at 09:30

score 5 · Accepted Answer · edited May 23 '17 at 10:32

5

An UPDATE statement with a REPLACE should be better in this case.

Look at this post, it might help you understand what I'm talking about :

Deleting part of a string in MYSQL

edited May 23 '17 at 10:32

Community

1
1

answered Jun 15 '12 at 09:16

BMN

8,253
14
48
80

Actually, this is better than my regex answer since, well, regex is unnecessary for a fixed string :-) + 1 and deleting mine. – paxdiablo Jun 15 '12 at 09:19
1

Yes, I though the REGEX solution was a bit complex for this kind of trick. But I have to add that, even if the OP doesn't want to talk about it, the structure of the table should be reviewed. – BMN Jun 15 '12 at 09:20
I still think you should be using like. On large tables what tsabz proposed won't be using any index. – mihaisimi Jun 15 '12 at 09:41
What I'm proposing is just a way of doing the trick. He's free to modify the request adding any kind of `like` clause or whatever he wants. – BMN Jun 15 '12 at 09:45

score 0 · Answer 2 · edited Jun 15 '12 at 10:22

0

You could use delete command to delete it:

DELETE FROM table_name
WHERE some_column=some_value

edited Jun 15 '12 at 10:22

TRR

1,637
2
26
43

answered Jun 15 '12 at 09:13

Jathin

79
1
1
8

Can't do that because it will delete all the data in the field "System" – Hafiz Abdullah Jun 15 '12 at 09:14
Give me a minute . Let me just find out another way !! – Jathin Jun 15 '12 at 09:16

score 0 · Answer 3 · answered Jun 15 '12 at 09:16

0

$searchQuery = "Event";

$query = mysql_query("SELECT `id`, `System` FROM `System_t` WHERE `System` LIKE '%" . $searchQuery . "%'");

if (mysql_num_rows($query) > 0) {
    while ($arr = mysql_fetch_row($query)) {
        $system = explode("\n", $arr[1]);
        $key = array_search($searchQuery, $system);
        if ($key !== false)
            unset($system[$key]);

        $system = implode("\n", $system);

        mysql_query("UPDATE `System_t` SET `System`='" . $system . "' WHERE `id`='" . $arr[0] . "'");
    }
}

answered Jun 15 '12 at 09:16

Ananth

4,227
2
20
26

I read from the question that the `Event` line will be a unique piece of text for each row rather than just the text `'Event'`. – bendataclear Jun 15 '12 at 09:19
`mysql_*` shouldn't be used anymore. – Shikiryu Jun 15 '12 at 09:20
@bendataclear LIKE '%text%' will find the rows even when there is a new line in between – Ananth Jun 15 '12 at 09:23
@Shikiryu Yeah, but it is easier to explain it using mysql_*, isn't it? – Ananth Jun 15 '12 at 09:23
@Ananth nope, at least use `mysqli_*`. Better use PDO, so that any beginners don't use `mysql_*` because some old dude find it easier. Let's **kill** `mysql_*` once and for all. – Shikiryu Jun 15 '12 at 09:26
@Shikiryu gotcha! Will surely do from next time :) – Ananth Jun 15 '12 at 09:30

mihaisimi · Answer 4 · 2012-06-15T09:29:41.733

0

Try this:

 update System_t su
       set su.System = replace(su.System, "Event", "") 
        where su.System like "%Event%";

I haven't taken the new lines into account I leave this for you as an easy exercise :).

edited Jun 15 '12 at 09:29

answered Jun 15 '12 at 09:23

mihaisimi

1,911
13
15

score 0 · Answer 5 · edited Jul 14 '12 at 21:47

0

Update system_t SET system=REPLACE(system,'event','') WHERE system REGEXP '[[:<:]]event[[:>:]]';

edited Jul 14 '12 at 21:47

Jason Sturges

15,855
14
59
80

answered Jun 15 '12 at 09:25

sel

4,982
1
16
22

Just notice there is a better solution provided by tsabz. – sel Jun 15 '12 at 09:29

score 0 · Answer 6 · answered Jun 15 '12 at 10:11

For PostgreSQL, you coud use something like (because I believe there's no direct replace function as in MySQL):

UPDATE "System_t"
SET "System" = overlay("System" placing '' 
               from (position('Events' in "System")) 
               for 6)
WHERE "System" LIKE '%Events%'

You can see the description of the overlay and position functions here: http://www.postgresql.org/docs/8.0/static/functions-string.html

And 6 is the size of the string you are removing.

Anyway, I would recommend for you to create another table with, for example, (id, system_id, system_type), with system_id being a foreign key poiting to the other table. That way, you would have a better model and avoid a lot of inconsistency.

delete complicated value from mysql database

6 Answers6