how to use regex in mysql UPDATE query?

I have 2 tables, one is a table of parameters that the other table can have, and they're stored like this:

Table 1

id         name
1          var1
2          var2
3          var3

Table 2

id         name       parameters
1          name1      var1|var3
2          name2      var2
3          name3      var1|var2|var3

Now, when I run a DELETE query, I want to update tab开发者_JS百科le 2 parameters aswell, so let's say I run a DELETE query on table 1, row 1, I want to get the following table 2 result, should I query for it:

Table 2

id         name       parameters
1          name1      var3
2          name2      var2
3          name3      var2|var3

Any help would be appreciated, Thanks

You should restructure your tables. Normalized data modelling would tell you to use an n-to-m table structure (many-to-many or junction). So create a third table which has all references to both of your tables and resides in between your current two tables.

If you have not yet solidified this code, I highly advise that you rework the schema before it becomes too difficult to manage.

The proper way to handle this situation in Table 2 is not to delimit the values which key to Table 1, but rather to store multiple rows per id. Then when deleting from Table 1, a FOREIGN KEY constraint can enforce ON DELETE CASCADE and this becomes a non-issue.

Example Table 2 data would look like:

id         name
1          name1
1          name1
2          name2
3          name3
3          name3
3          name3

The definition of Table 2 would look like:

CREATE TABLE Table_2
(
  id INTEGER NOT NULL,
  name VARCHAR() NOT NULL,
  FOREIGN KEY fk_id (id) REFERENCES Table_1 (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

imho you have a small DB design problem here. You must normalize the table 2 by doing:

id pk name UK parameter UK

parameter should have 1 value. So your row no 3 should be splitted in two rows:

3 name3 var2
4 name3 var3

Or if it is too complicated, do like cularis said, add a third table.