MySQL multiple table update & Regular Expression

Hello all

Though its a very simple process but we usually don’t do this stuff too much unless we are bound to do that.In this article I will show how to update a table data with another table depending on another table & another will be a simple regular expression in MySQL query.

In the example I have ‘new_users’ table to update with the data of ‘listings’ table depending on the condition or ‘users’ table. and the table structure is as bellow.

‘new_users’ :
userID,phone,user_state
‘listings’ :
listID,userID,phone
‘users’:
countID,userID,category

Now the scenario is I have to update the phone of new_users from the listings tables phone of same userID but only who are only ‘Admin’ which we may found in users table’s  category field. So the query will be as following

Now come to the second part, we can apply regular expression in our MySQL query as follow:

The query will set null to user_state field if user_state field contains none but a-z & A-Z previously.

I have also some collection or regular expression for MySQL which i have collected from different site.

A very simple example illustrating this is to select all the records from MyTable for which MyField starts with "A"

SELECT * FROM  MyTable WHERE MyField REGEXP ‘^a’;

Please take a look to the list below in order to find more information for the available options MySQL Regular Expressions

Matches zero or more instances of the string preceding it

Matches one or more instances of the string preceding it

Matches zero or one instances of the string preceding it

Matches any single character
[xyz] 
Matches any of x, y, or z (the characters within the brackets)
[A-Z] 
Matches any uppercase letter
[a-z] 
Matches any lowercase letter
[0-9] 
Matches any digit 

Anchors the match from the beginning 

Anchors the match to the end

Separates strings in the regular expression
{n,m} 
String must occur at least n times, but no more than n  
{n} 
String must occur exactly n times
{n,| 
String must occur at least n times

[Source]

 

That’s all for the day.
BYE

User ScrumPad for your Agile based projects.