UPDATE REGEX MYSQL

Question

I have a table of contacts and a table of postcode data.

I need to match the first part of the postcode and the join that with the postcode table... and then perform an update...

I want to do something like this...

UPDATE `contacts` LEFT JOIN `postcodes` ON PREG_GREP("/^[A-Z]{1,2}[0-9][0-9A-Z]{0,1}/", `contacts`.`postcode`) = `postcodes`.`postcode` SET `contacts`.`lat` = `postcode`.`lat`, `contacts`.`lng` = `postcode`.`lng`

Is it possible?? Or do I need to use an external script?

Many thanks.

Answer 1

Im not sure what you are using the regex for but if you just need the first [LENGTH] characters to be the same this will work:

update c
set c.lat = p.lat, c.lng = p.lng
from contacts c
inner join postcodes p
  on substring(c.postcode, 0, [LENGTH]) = substring(p.postcode, 0, [LENGTH]

Answer 2

I decided to approach it from a different angle... there might be a few clashes but I can live with that.

UPDATE fellow f INNER JOIN postcode p ON f.postcode LIKE CONCAT(p.postcode, "%") SET f.lat = p.lat, f.lng = p.lng