How can I copy data from one column to another in the same table?

Question

Is it possible to copy data from column A to column B for all records in a table in SQL?

score 745 · Accepted Answer · edited Sep 28 '18 at 08:55

745

How about this

UPDATE table SET columnB = columnA;

This will update every row.

edited Sep 28 '18 at 08:55

wintermeyer

8,178
8
39
85

answered Jun 10 '11 at 15:32

Ash Burlaczenko

24,778
15
68
99

2

This will also work if you want to transfer old value to other column and update the first one: `UPDATE table SET columnA = 'new value', columnB = columnA`. Like other answer says - don't forget the WHERE clause to update only what's needed. – Carl di Ortus Feb 09 '18 at 08:45
10

I thinking in some complex proc doing a loop at each record when the answer is so simple – deFreitas Apr 27 '18 at 01:41
8

This shows the power of a declarative language! Most of us are so familiar with imperative programming languages that we forget simplicity. – codeara Aug 11 '20 at 22:02

score 161 · Answer 2 · edited Sep 03 '15 at 10:55

161

UPDATE table_name SET
    destination_column_name=orig_column_name
WHERE condition_if_necessary

edited Sep 03 '15 at 10:55

Christian Phillips

18,399
8
53
82

answered Feb 26 '14 at 12:45

dev4092

2,820
1
16
15

14

@Mark, this actually makes sense, why is this downvoted? I had a situation where I need to copy a date value from one column to other columns, and it only applies to SOME of the columns, and not all. So having a WHERE in the statement would be necessary. – finnTheHumin May 09 '14 at 06:20
6

@finnTheHuman The question asks how to " copy data from all records" which the earlier answer answers correctly so this does not add anything – mmmmmm May 09 '14 at 11:31
10

@Mark "How does this differ from the old accepted answer", it has a WHERE clause. "so this does not add anything", I disagree, it adds something. it's a good point to include WHERE clause IF NECESSARY. we should be responsible on downvoting answers. haters gon' hate – finnTheHumin May 17 '14 at 05:33
In my case I added a last_seen column. `UPDATE user SET last_seen = first_seen WHERE last_seen IS NULL` – luckydonald Jan 18 '17 at 18:57
...but I need a clause in mine that limits it to 100 records /s – Nick T May 03 '19 at 02:58

score 5 · Answer 3 · answered Jun 24 '20 at 05:36

This will update all the rows in that columns if safe mode is not enabled.

UPDATE table SET columnB = columnA;

If safe mode is enabled then you will need to use a where clause. I use primary key as greater than 0 basically all will be updated

UPDATE table SET columnB = columnA where table.column>0;

score 1 · Answer 4 · answered Nov 11 '21 at 19:57

If you want to copy a column to another column with a different data type in PostgresSQL, you must cast/convert to the data type first, otherwise it will return

Query 1 ERROR: ERROR: column "test_date" is of type timestamp without time zone but expression is of type character varying LINE 1: update table_name set test_date = date_string_col ^ HINT: You will need to rewrite or cast the expression.

An example of converting varchar to timestamp:

update table_name set timestamp_col = date_string_col::TIMESTAMP;

An example of converting varchar to int:

update table_name set int_column = string_col::INTEGER;

but any column type(except file or the similar) can be copied to string(character varying) without cast the type.

How can I copy data from one column to another in the same table?

4 Answers4

Linked

Related