mysql update column with value from another table

mysql sql sql-update
LeoSam picture LeoSam · Jul 29, 2012 · Viewed 372.2k times · Source

I have two tables, both looking like

id  name  value
===================
1   Joe     22
2   Derk    30

I need to copy the value of value from tableA to tableB based on check name in each table.

Any tips for this UPDATE statement?

Answer

RafaSashi picture RafaSashi · Oct 13, 2013

In addition to this answer if you need to change tableB.value according to tableA.value dynamically you can do for example:

UPDATE tableB
INNER JOIN tableA ON tableB.name = tableA.name
SET tableB.value = IF(tableA.value > 0, tableA.value, tableB.value)
WHERE tableA.name = 'Joe'

Related questions

Multiple Updates in MySQL

I know that you can insert multiple rows at once, is there a way to update multiple rows at once (as in, in one query) in MySQL? Edit: For example I have the following Name id Col1 Col2 Row1 1 6 1 Row2 2 2 3 …

Read More
MySQL - UPDATE multiple rows with different values in one query

I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand. For instance, three updates into 1 query: UPDATE table_users …

Read More
Increment value in mysql update query

I have made this code for giving out +1 point, but it doesn't work properly. mysql_query(" UPDATE member_profile SET points= ' ".$points." ' + 1 WHERE user_id = '".$userid."' "); the $points variable is the user´s points right now.. …

Read More