CODEWITHSUNDEEP
sql
jonney
jonney

Reputation: 4395

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

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

Upvotes: 427

Views: 518887

Answers (4)

Muhammad Dyas Yaskur
Muhammad Dyas Yaskur

Reputation: 8138

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.

Upvotes: 4

Vayuj Rajan
Vayuj Rajan

Reputation: 107

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;

Upvotes: 7

Ash Burlaczenko
Ash Burlaczenko

Reputation: 25475

How about this

UPDATE table SET columnB = columnA;

This will update every row.

Upvotes: 773

dev4092
dev4092

Reputation: 2891

UPDATE table_name SET
    destination_column_name=orig_column_name
WHERE condition_if_necessary

Upvotes: 166

Related Questions