CODEWITHSUNDEEP
mysqlsqlmariadb
zufanka
zufanka

Reputation: 47

Mysql: Update values of one column with values from multiple colums

There are about 12 tables in my DB with many columns. I would like to pick information I need from them and put it into a new table with a pre-defined structure. All tables have an unique identifier "ID".

newtable structure:

ID | apples | bananas | cucumbers | dates

table1 structure

ID | chiquita | grannysmith | IDontWanthis | OrThis

Using:

UPDATE newtable SET bananas = (SELECT chiquita FROM table1
                               WHERE newtable.ID = table1.ID)

I have however difficulties when more columns can hold the piece of information I need to fill in the new column.

I tried:

UPDATE newtable SET apples = (SELECT grannysmith FROM table1
                              WHERE newtable.ID = table1.ID)

and then a new update:

UPDATE newtable SET apples = (SELECT elstar FROM table2
                              WHERE newtable.ID = table2.ID
                              AND newtable.apples IS NULL)

It however replaces all the values in newtable.apples with table2.elstar, not only the NULL values. The values that were filled in previously are now NULL.

I'm very new to SQL and don't know what I'm doing wrong. Is there maybe a more efficient way to do this? Thanks for support!

Upvotes: 0

Views: 686

Answers (2)

Alex
Alex

Reputation: 17289

You should better use JOIN:

http://sqlfiddle.com/#!9/f3dda/1

UPDATE newtable n
INNER JOIN table1 t
ON n.id = t.id
SET n.bananas = t.chiquita, n.apples = t.grannysmith

and if you want to avoid overwrite old value in newtable you can change ON clause like:

UPDATE newtable n
INNER JOIN table1 t
ON n.id = t.id AND (n.bananas IS NULL OR n.apples IS NULL)
SET n.bananas = t.chiquita, n.apples = t.grannysmith

UPDATE Even if you want to update just one column per time JOIN is more preferable way to do that from preformance perspective:

UPDATE newtable n
INNER JOIN table1 t
ON n.id = t.id AND n.bananas IS NULL 
SET n.bananas = t.chiquita

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

UPDATE newtable SET apples = (SELECT elstar FROM table2
                          WHERE newtable.ID = table2.ID
                          AND newtable.apples IS NULL)
WHERE apples IS NULL

You need a where clause to filter for apples is null in the outer query as well.

Upvotes: 1

Related Questions