SOLVED dynamic like

[niche]

I have 4 street addresses in a table called addr:123 MAIN ST123 N MAIN ST123 S MAIN ST123 E MAIN ST

 

I have 4 street directions in a table called dir:NSEW

 

I could write 4 separate queries based on:UPDATE addr SET dir='N' WHERE addr LIKE '% N %'

 

Is there a way to do it in one query? If so how?

 

I tried:

 

UPDATE addr, dir SET addr.dir = dir.dir WHERE addr.addr LIKE '% dir.dir %'

 

But, no joy (though it didn't cause any errors).

 

Edited September 14, 2015 by niche

[davej]

I don't understand the columns of your tables.

[niche]

The addr table has two cols: addr & dir

 

the addr col contains the address and the dir col is empty (its the col I want to update)

 

The dir table has a single col called dir (short for direction).

[davej]

I cannot think why you would want to have a table with a single column? And why add a record with addr but without dir?

[niche]

my ultimate objective is to parse street addresses using only queries. my current tables are partial tables so I can learn dynamic likes.

[davej]

I suspect you would need to write a stored procedure to do that.

[niche]

Turns out this is the solution:

UPDATE addr, dir SET addr.dir = dir.dir WHERE addr.addr LIKE CONCAT('% ',dir.dir,' %')

But, I can't find a ref that would've of prompted me to expect to treat the argument as a string. Is it in the manual?

Edited September 14, 2015 by niche

[justsomeguy]

Why wouldn't it be a string? When you do this:

LIKE '% dir.dir %'

You have that text inside a string. You're telling to search for the text " dir.dir ". It's not going to look inside that string and try and figure out if there are any table and column names in it, it expects a string because you put the quotes there to tell it that it's a string of text. Treat column and table names like variables in other languages.

[niche]

Treat column and table names like variables in other languages.

 

Got it. Thank-you jsg.