CODEWITHSUNDEEP
mysqljoin
ol30cean0
ol30cean0

Reputation: 501

Join table in order to create field with data from different tables

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

Upvotes: 1

Views: 46

Answers (1)

Paul Roub
Paul Roub

Reputation: 36448

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);

Upvotes: 1

Related Questions