MySQL update based upon postal code

i have a usertable which includes a field named zip (postal code) I also have a geo table with latitude, longitude values, based upon zipcodes.

so:

usertable 
table_1 contains fields: zip, latitude,longitude (in last 2 fields all values are NULL)
geotable:
table_2 contains fields: zip, latitude,longitude

The zipcode in table_1 has th开发者_Python百科is format: 1111 AA (4 numbers, 2 letters, usually split by empty character)

The zipcode in table_2 has format: 1111 (only four numbers)

I am trying to find out how an update query should look like?

I want to update the usertable with the values from the geotable, so a search query can be done without any joins. Search queries are the most used queries on this site, The updates/checks seldom.

Thanks in advance for any help!

You could write

UPDATE table_1 SET
  latitude = (SELECT latitude FROM table_2 WHERE table_1.zip LIKE CONCAT(table_2.zip, '%')),
  longitude = (SELECT longitude FROM table_2 WHERE table_1.zip LIKE CONCAT(table_2.zip, '%'));

Unfortunately I don't believe there is syntax quite similar to what Colin is suggesting. You may find it more efficient to simply select the values from table_2 first and then insert them.

You can use an UPDATE ... JOIN clause to join the tables and perform the update:

update
    usertable ut inner join geotable gt on
        ut.zip like concat(gt.zip, '%')
    set ut.latitude = gt.latitude,
        ut.longitude = gt.longitude;

Demo: http://www.sqlize.com/398E9CKddr

Untested, and I may not have the syntax quite right, but this is the principle:

UPDATE table_1  COLUMNS(latitude, longitude) VALUES (
   SELECT latitude, longitude FROM table_2 
      where table_1.zip LIKE CONCAT(table_2.zip, ' %'));