views:

24

answers:

2

I have two tables, t1 and t2 with two columns each - id_user and age.
How do I update t1.age to the greatest of t1.age and t2.age for matching ID's and leave t1.age unchanged if there is no matching ID in t2.

Before update:

t1  
+-------+---+   
|id_user|age|  
+-------+---+   
|      1|  5|  
+-------+---+   
|      2| 10|  
+-------+---+   
|      3| 10|  
+-------+---+   

t2
+-------+---+   
|id_user|age|  
+-------+---+   
|      2| 12|  
+-------+---+   
|      3|  8|  
+-------+---+   
|      4| 20|  
+-------+---+   

After update:

t1  
+-------+---+   
|id_user|age|  
+-------+---+   
|      1|  5|  
+-------+---+   
|      2| 12|  
+-------+---+   
|      3| 10|  
+-------+---+   
A: 
UPDATE t1
SET age = T2.age
FROM t1
INNER JOIN t2
ON t2.id_user = t1.id_user
WHERE t2.age > t1.age
Will A
@Will: I don't think you *don't* need a `FROM` in there :)
Daniel Vassallo
Really? I come from a SQL Server background where such niceness is required - thanks for the heads up.
Will A
MySQL won't appreciate :) ... BTW: I had a typo in the above comment. I obviously intended "I *don't* think you need a `FROM`..."
Daniel Vassallo
:) - the MySQL syntax does look pretty clean for this, I'll bear it in mind for the future. I got what you meant, sir - I didn't think I didn't need a FROM. ;)
Will A
+2  A: 

You may want to try:

UPDATE  t1
JOIN    t2 ON (t2.id_user = t1.id_user)
SET     t1.age = t2.age
WHERE   t2.age > t1.age;

Test Case:

CREATE TABLE t1 (id_user int, age int);
CREATE TABLE t2 (id_user int, age int);

INSERT INTO t1 VALUES (1, 5);
INSERT INTO t1 VALUES (2, 10);
INSERT INTO t1 VALUES (3, 10);

INSERT INTO t2 VALUES (2, 12);
INSERT INTO t2 VALUES (3, 8);
INSERT INTO t2 VALUES (4, 20);

Result:

SELECT * FROM t1;
+---------+------+
| id_user | age  |
+---------+------+
|       1 |    5 |
|       2 |   12 |
|       3 |   10 |
+---------+------+
3 rows in set (0.00 sec)
Daniel Vassallo