niche Posted September 11, 2015 Share Posted September 11, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
davej Posted September 11, 2015 Share Posted September 11, 2015 I don't understand the columns of your tables. Link to comment Share on other sites More sharing options...
niche Posted September 12, 2015 Author Share Posted September 12, 2015 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). Link to comment Share on other sites More sharing options...
davej Posted September 12, 2015 Share Posted September 12, 2015 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? Link to comment Share on other sites More sharing options...
niche Posted September 12, 2015 Author Share Posted September 12, 2015 my ultimate objective is to parse street addresses using only queries. my current tables are partial tables so I can learn dynamic likes. Link to comment Share on other sites More sharing options...
davej Posted September 13, 2015 Share Posted September 13, 2015 I suspect you would need to write a stored procedure to do that. Link to comment Share on other sites More sharing options...
niche Posted September 14, 2015 Author Share Posted September 14, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted September 14, 2015 Share Posted September 14, 2015 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. Link to comment Share on other sites More sharing options...
niche Posted September 14, 2015 Author Share Posted September 14, 2015 Treat column and table names like variables in other languages. Got it. Thank-you jsg. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now