1

I need to update a field named "tipos" in a table named azz_properties with values from other tables named azz_locality (field name is "name") and azz_category (field name is also "name"), using a word to separate those values, the word "in". Substantially I need to create a mini-description phrase, like "Property Category in Property Locality", Ex. House in Rome.

Also, I need to update the value only if it is empty.

I tried the following code but i receive "0 lines affected"

update azz_properties p join
   azz_locality l
   on p.id = l.id join
   azz_category c
   on p.id = c.id
set p.tipos = concat(c.name, ' in ', l.name);

Anyone can help me please? What am I doing wrong?

below are some lines from each table, I tried to make this visible in a good way but this is the best I could do, sorry for it...:

Table azz_category

id  name    alias   parent  published   ordering
17  Apartamentos    apartamentos    0   1   0
18  Casas   casas   0   1   1
19  Casas em condominios    casas-em-condominios    0   1   2
20  Coberturas  coberturas  0   1   3

Table azz_locality

id  parent  mid     zipcode     name    alias   published   ordering    checked_out     checked_out_time
1   1   0   0   Abraão  abraao  1   0   0   0000-00-00 00:00:00
2   1   0   0   Armação     armacao     1   0   0   0000-00-00 00:00:00
3   1   0   0   Agronômica  agronomica  1   0   0   0000-00-00 00:00:00
5   1   0   0   Bairro de Fatima    bairro-de-fatima    1   0   0   0000-00-00 00:00:00
6   1   0   0   Balneário Estreito  balneario-estreito  1   0   0   0000-00-00 00:00:00
7   1   0   0   Barra da Lagoa  barra-da-lagoa  1   0   0   0000-00-00 00:00:00
9   1   0   0   Beira Mar   beira-mar   1   0   0   0000-00-00 00:00:00
10  1   0   0   Bela Vista  bela-vista  1   0   0   0000-00-00 00:00:00
168     19  0   0   Siriú   siriu   0   0   0   0000-00-00 00:00:00

This is azz_properties, where category id is "cid" field and locality id is "lid" 

id  name    name_tipos  name_barrios    alias   parent  agent_id    agent   ref     type    cid     lid     sid     cyid    postcode    address     description     text    text_es     text_en     text_barrios    tipos   price   published   use_booking     ordering    panoramic   video   lat     lng     available   featured    years   bedrooms    bathrooms   garage  area    covered_area    hits    listdate    refresh_time    checked_out     checked_out_time 
2920    Vendo Apartamento...    Vendo Apartamento...        vendo-apartamento...    0   62      A3044   62  17  3   1   1       Rua Silveira    Agenciamento...     <p>Apartamento ...  360000.00   1   0   0   NULL        0.000000    0.000000    0   0   2012.01.01.05110    3   2   1   105     90  231     2013-05-03  2013-05-03 00:00:00     0   0000-00-00 00:00:00
ol30cean0
  • 481
  • 4
  • 8
  • 18
  • 1
    Can you show a few line of sample data from each table? It seems odd that the IDs would be the same; typically, `azz_properties` would contain *other* fields referencing `azz_category.id` and `azz_locality.id` – Paul Roub Sep 29 '13 at 19:10
  • ok sorry!!! I am doing this! – ol30cean0 Sep 29 '13 at 19:23

1 Answers1

1

Seems like what you need, given the data, is:

update azz_properties p join
   azz_locality l
   on p.lid = l.id join
   azz_category c
   on p.cid = c.id
set p.tipos = concat(c.name, ' in ', l.name);
Paul Roub
  • 36,322
  • 27
  • 84
  • 93
  • it works thank you so much!! I have one last problem, the data I have now in field tipos is like: Apartamentos in Agronomica and i need it without "s", like Apartamento in Agronomica, is there a solution?? – ol30cean0 Sep 29 '13 at 19:48
  • You *could* look at something like http://stackoverflow.com/questions/1755408/mysql-regex-replace to see about replacing trailing "s" characters, but that won't work well for things like "Casas em condominios" where you'd want "Casa em condominio" or "Casa ou condomínio". If you want to be careful about that, I'd add a separate table of singular category names (with the same keys as `azz_category`), outer join that in, and use `coalesce(singular.name, c.name)` inside your `concat` to get the singular version (if any), or fall back on the original. – Paul Roub Sep 29 '13 at 20:30
  • yes, great idea, adding a separate table with same category ids and naomes without S! Thank you, you are very smart! – ol30cean0 Sep 29 '13 at 21:38