418

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

Improve this question
0

4 Answers 4

Reset to default
761

How about this

UPDATE table SET columnB = columnA;

This will update every row.

Improve this answer
3
  • 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
    Commented Feb 9, 2018 at 8:45
  • 11
    I thinking in some complex proc doing a loop at each record when the answer is so simple
    – deFreitas
    Commented Apr 27, 2018 at 1: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
    Commented Aug 11, 2020 at 22:02
165 
UPDATE table_name SET
    destination_column_name=orig_column_name
WHERE condition_if_necessary
Improve this answer
5
  • 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
    Commented May 9, 2014 at 6: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
    Commented May 9, 2014 at 11:31
  • 11
    @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
    Commented May 17, 2014 at 5:33
  • In my case I added a last_seen column. UPDATE user SET last_seen = first_seen WHERE last_seen IS NULL
    – luckydonald
    Commented Jan 18, 2017 at 18:57
  • ...but I need a clause in mine that limits it to 100 records /s
    – Nick T
    Commented May 3, 2019 at 2:58
6

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;

Improve this answer
3

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.