CODEWITHSUNDEEP
mysqlsql-update
ghukill
ghukill

Reputation: 1222

Updated rows in MYSQL from Table View rows

I'm stumped.

Let's say I've got a table, '1', with three columns, 'A', 'B', 'C'. Column 'C' has some NULL values. Another table, '2', has columns 'A' (that matches table '1') and 'C', where 'C' is complete.

How can I merge the values from table '2' into table '1' in MYSQL?

I've tried, and swore up and down it should work:

UPDATE 1  
SET 1.C = 2.C  
FROM 1 JOIN 2  
ON 1.A = 2.A  
WHERE 1.C IS NULL;

And clues? hints? ideas?

Upvotes: 0

Views: 1175

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43464

This works:

update t1, t2
set t1.c = t2.c
where t1.a = t2.a and t1.c is null;

Answer updated based on requirements changed. T2 could be a view, so a join might be a better idea. I don't know much about views, though, so I just moved out of the UPDATE line t2... but I'm not sure if it changes anything.

update t1
join t2 on t1.a = t2.a
set t1.c = t2.c
where t1.c is null;

Upvotes: 1

John Woo
John Woo

Reputation: 263803

your query is correct but try using Alias:

UPDATE  `tableNameA` `a`  
SET     `a`.`C` = `b`.`C`  
FROM    `a` INNER JOIN `tableNameB` `b` ON `a`.`A` = `b`.`A`  
WHERE   `a`.`C` IS NULL;

I added backtick in case some of your fields contains RESERVED WORD

Upvotes: 0

Related Questions