CODEWITHSUNDEEP
mysqldatabase
Lucas
Lucas

Reputation: 2984

Copy values from one column to another in the same table

How can I make a copy values from one column to another?

I have:

Database name: list

-------------------
number | test
-------------------
123456 | somedata
123486 | somedata1
232344 | 34

I want to have:

Database name: list

----------------
number | test
----------------
123456 | 123456
123486 | 123486
232344 | 232344

What MySQL query should I have?

Upvotes: 218

Views: 240875

Answers (8)

Karan Kumar Mahto
Karan Kumar Mahto

Reputation: 103

update `table`
set `firstcolumn` = `secondcolumn`

Upvotes: -8

M. Abrar Rubaiyat Islam
M. Abrar Rubaiyat Islam

Reputation: 11

IF Anyone wants to put Condition

UPDATE bohf SET Sq=IDNo WHERE Table = 'AOF' AND FormSq BETWEEN 13 AND 17

Upvotes: 0

Jigneshsinh Rathod
Jigneshsinh Rathod

Reputation: 1028

try following:

UPDATE `list` SET `test` = `number`

If list is table name and test and number are columns

it creates copy of all values from "number" and paste it to "test"

Upvotes: 9

Juicy Scripter
Juicy Scripter

Reputation: 25918

Short answer for the code in question is:

UPDATE `table` SET test=number

Here table is the table name and it's surrounded by grave accent (aka back-ticks `) as this is MySQL convention to escape keywords (and TABLE is a keyword in that case).

BEWARE!

This is pretty dangerous query which will wipe everything in column test in every row of your table replacing it by the number (regardless of it's value)

It is more common to use WHERE clause to limit your query to only specific set of rows:

UPDATE `products` SET `in_stock` = true WHERE `supplier_id` = 10

Upvotes: 461

zzapper
zzapper

Reputation: 5043

BEWARE : Order of update columns is critical

GOOD: What I want saves existing Value of Status to PrevStatus

UPDATE Collections SET  PrevStatus=Status, Status=44 WHERE ID=1487496;

BAD: Status & PrevStatus both end up as 44 

UPDATE Collections SET  Status=44, PrevStatus=Status WHERE ID=1487496;

Upvotes: 10

nitinr708
nitinr708

Reputation: 1467

Following worked for me..

  1. Ensure you are not using Safe-mode in your query editor application. If you are, disable it!
  2. Then run following sql command

for a table say, 'test_update_cmd', source value column col2, target value column col1 and condition column col3: -

UPDATE  test_update_cmd SET col1=col2 WHERE col3='value';

Good Luck!

Upvotes: 6

Czechnology
Czechnology

Reputation: 14992

UPDATE `table_name` SET `test` = `number`

You can also do any mathematical changes in the process or use MySQL functions to modify the values.

Upvotes: 37

juergen d
juergen d

Reputation: 204746

try this:

update `list`
set `test` = `number`

Upvotes: 14

Related Questions