Update table using alias

user_2919395 0
Viewed 19516

I need to fill some fields in a table getting informations from other records of the same table. I tried to write a query to explain what I want to do:

update globale2
set
  nita = t.nita,
  tita = t.tita,
  notaita = t.notaita
where
  neng = t.neng and
  nita is null
  (select nita, neng, tita, notaita from globale where uris='mma' and nita is not null) as t

edit to eplain better:

every records have these fields: "nita", "tita", "notaita", "neng" ("neng" cannot be null)

I want to fill these fields: "nita", "tita", "notaita" (where "nita" is empty) with the same values from another record where "neng" equals the other "neng"

mysql sql
2 Answers

You can however, join the two tables.

UPDATE  globale2 g
        INNER JOIN globale gg
            ON g.neng = gg.neng
SET     g.nita = gg.nita,
        g.tita = gg.tita,
        g.notaita = gg.notaita
WHERE   g.nita IS NULL
        AND gg.uris = 'mma' 
        AND gg.nita IS NOT NULL
user_4912430

assume there is a table A_temp, with two columns 'one' and 'two'. TABLE A_temp

ONE    TWO
1       2

this is the present status of the table.

The query 

UPDATE (SELECT * FROM A_temp ) A SET one = A.two where one = '1'

updates the table as 

ONE    TWO
2      2

Hope you get the idea and that it helps..

user_30562300
Related