CODEWITHSUNDEEP
mysqlinsertreplace
Mokus
Mokus

Reputation: 10400

Mysql update data

UPDATE

+-----+------------------+-------------+
| id  | datenum          | F1_baro_20_ |
+-----+------------------+-------------+
|   1 | 734152.000000000 |     1005.21 |
|   2 | 734152.006944445 |     1005.26 |
+-----+------------------+-------------+

this is my table

And this is my new table

+-----+------------------+-------------+------------+
| id  | datenum          | F1_baro_20_ |new column  |
+-----+------------------+-------------+------------+
|   1 | 734152.000000000 |     1005.21 |            |
|   2 | 734152.006944445 |     1005.26 |            |
+-----+------------------+-------------+------------+

I want to insert data to the 'new_colum' where old_datenum=new_datenum but I don't wan to modify the existing data!

Upvotes: 2

Views: 120

Answers (3)

Mattis
Mattis

Reputation: 5096

Try this.

Add a new column with the following SQL query:

ALTER TABLE myTable ADD newColumn <datatype>

Where <datatype> can be something like int, text, longtext, varchar(50) - depending on what you need.

Then update only this column with SQL queries like this:

UPDATE TABLE myTable SET newColumn = 'Hello this is my data'

or if you need to update a specific row (chosen where the column id is 2):

UPDATE TABLE myTable SET newColumn = 'Hello this is my data' WHERE id = '2'

Here's the simple SQL query to copy the data from datenum to newColumn:

UPDATE myTable SET newColumn = datenum

Run that code once (but with your table name and column name of course) and you'll see that the data is copied.

Or if you want to do it with, for example, PHP:

$rs = mysql_query('SELECT * FROM myTable ORDER BY id ASC');
while ($row = mysql_fetch_array($rs)) {
  $id = $row['id'];
  $new_datenum = $row['datenum'];
  $q = mysql_query('UPDATE myTable SET newColumn = $new_datenum WHERE id = $id');
}

This will also copy all the data from datenum to newColumn.

Upvotes: 1

Jai
Jai

Reputation: 3609

You can simply modify the old table to add the column.. If you cant do that, then just use a join on the two tables like this

UPDATE newTable N INNER JOIN
oldTable O 
ON N.datenum = O.datenum
SET N.newcolumn = <whatever you want to set>
WHERE <any conditions on either tables>

Upvotes: 0

Raoul
Raoul

Reputation: 3889

update mytable set newcol = 'yourvalhere' where somecol='somevalue';

The Where clause is optional, if you want to add the same value to the entire table ignore it. For more info post an example of your schema and what you want the data to be.

See also

http://dev.mysql.com/doc/refman/5.0/en/update.html

Upvotes: 0

Related Questions