Updating each row separately

I'm trying to update some rows in my database.

I have a table like the following :

id | subid | creation_date

1  | 1/1   | 2011-06-23

1  | 1/2   | 0000-00-00

2  | 2/1   | 2011-06-20

2  | 2/2   | 0000-00-00

WHat i wan开发者_JAVA技巧t is to update the entries having the creation_date set to "0000-00-00" with the creation_date of the one who have a real date.

The result after the request would be :

id | subid | creation_date

1  | 1/1   | 2011-06-23

1  | 1/2   | 2011-06-23

2  | 2/1   | 2011-06-20

2  | 2/2   | 2011-06-20

Can someone out there have an idea to help me ? Il would be perfet to make this with a single request.

Thanks ;)

B.

---

to get around the problem with that other answer of not being able to have the table in the sub query that you are updating. let's just create at temp table and use that...

CREATE TEMPORARY TABLE foo SELECT id, MAX(creation_date) FROM yo_table GROUP BY id;
UPDATE yo_table SET creation_date = ( SELECT foo.creation_date FROM foo WHERE foo.id = yo_table.id ) 
WHERE creation_date = '0000-00-00';

---

update yo_table outter
set creation_date = 
    (select min(creation date) from yo_table iner where iner.id = outter.id)
where creation_date = '0000-00-00' --note that you'll have to edit this according to the data type of your creation_date column

Edit: with temp. table

create table yo_table_tmp as select * from yo_table;

    update yo_table outter
    set creation_date = 
        (select min(creation date) from yo_table_tmp iner where iner.id = outter.id)
    where creation_date = '0000-00-00' --note that you'll have to edit this according to the data type of your creation_date column
;

drop table yo_table_tmp;

---

update table_a as t1, table_a as t2
set t1.creation_date=t2.creation_date
where t1.id=t2.id and (t1.creation_date=0 and t2.creation_date>0);

---

I think this should work for you:

UPDATE `tableA` `ta`
INNER JOIN (
    SELECT `id`, `creation_date`
    FROM `tableA`
    WHERE `creation_date` > '0000-00-00'
    GROUP BY id
) `tb` ON `ta`.`id` = `tb`.`id`
SET `ta`.`creation_date` = `tb`.`creation_date`
WHERE `ta`.`creation_date` = '0000-00-00';

Hope this helps.

---

Create a temporary table.

I modified your subid for simplicity you can always combine them in the query result.

mysql> update table1 set creation_date = (SELECT x.creation_date
    from (SELECT * from table1 WHERE subid=1) AS X
    WHERE x.id =table1.id) WHERE subid=2;

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from table1;
+----+-------+---------------+
| id | subid | creation_date |
+----+-------+---------------+
|  1 |     1 | 2011-06-23    |
|  1 |     2 | 2011-06-23    |
|  2 |     1 | 2011-06-20    |
|  2 |     2 | 2011-06-20    |
+----+-------+---------------+
4 rows in set (0.00 sec)