SQL update a table

Question

TABLE 1

 id   name        uf        ibge
 1   GOIANIA     'GO'       null 
 2   BRASILIA    'DF'       null
 3   TOCANTINS   'TO'       null

TABLE 2

 id      name            uf       ibge
 1    GOI**Â**NIA       'GO'     5208707
 2    BRAS**Í**LIA      'DF'     5300108
 3    TOCANTINOPOLIS    'TO'     1721208

I need to update in table 1, the field ibge using some kind of like in the where clause to relate the description of the city with ibge code.

Could anyone help? Very grateful!

Sorry, could you be please give more details. And as far as I see you would be able to match records by the `uf` column. — adrian7, Feb 14 '14 at 12:18

StuartLC · Answer 1 · 2014-02-14T12:35:58.617

1

Assuming you can join both tables on the uf field, like so:

UPDATE Table1
  SET ibge = Table2.ibge
  FROM 
    Table2
  WHERE 
    Table1.uf = Table2.uf;

Fiddle here

edited Feb 14 '14 at 12:35

answered Feb 14 '14 at 12:18

StuartLC

104,537
17
209
285

score 1 · Answer 2 · answered Feb 14 '14 at 12:19

1

Try this...

UPDATE T1
SET T1.ibge = T2.ibge
FROM Table1 T1
INNER JOIN Table2 T2 
ON T1.id = T2.id

answered Feb 14 '14 at 12:19

chris_techno25

2,401
5
20
32

Who downvoted this answer? I really don't get it. I tried this on my SQL Server. It works. Whoever downvoted this probably doesn't know SQL. Whoever you are, please do leave a comment. I'm okay if I made a mistake. I stand corrected. But please provide a better answer. – chris_techno25 Feb 14 '14 at 12:35
1

Same here. Presumably the downvoter is fixating on the OP's requirement is to do some kind of proximity or Regex match to the proprietary encoding on the Table2 `name`s. – StuartLC Feb 14 '14 at 12:39
2

Yes Sir StuartLC and Anri. We try to provide a valid answer and people just hate on it. The OP just needed to update ibge in Table1 and he suggested some kind of solution. And since only fields id and uf are possible to use as reference, I just used the id field as connection to the second table. If the OP asked this kind of question, he probably doesn't realize the field 'name' cannot be used because values are different. That's why we guide and provide answers. Like I said, downvoting is alright, but whoever you are, provide a better answer :) – chris_techno25 Feb 14 '14 at 12:47

score 0 · Answer 3 · answered Feb 14 '14 at 12:24

0

UPDATE table1 tab1 SET ibge = (SELECT ibge FROM table2 tab2 WHERE tab1.name like (substring(tab2.name, 1, 3) || '%') LIMIT 1);

Assuming that the first 3 characters are the same in field name for table 1 and table 2

answered Feb 14 '14 at 12:24

Kevin Joymungol

1,764
4
20
30

SQL update a table

3 Answers3