Update table using alias

Question

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"

John Woo · Accepted Answer · 2013-12-04 12:53:52Z

19

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

answered Dec 4, 2013 at 12:53

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Thomas · Accepted Answer · 2013-12-04 13:09:12Z

0

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..

answered Dec 4, 2013 at 13:09

Tom Thomas

6292 gold badges8 silver badges24 bronze badges

Collectives™ on Stack Overflow

Update table using alias

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related