Is it possible to copy data from column A to column B for all records in a table in SQL?
Add a comment
|
5 Answers
How about this
UPDATE table SET columnB = columnA;
This will update every row.
3 Comments
Carl di Ortus
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.
deFreitas
I thinking in some complex proc doing a loop at each record when the answer is so simple
codeara
This shows the power of a declarative language! Most of us are so familiar with imperative programming languages that we forget simplicity.
UPDATE table_name SET
destination_column_name=orig_column_name
WHERE condition_if_necessary
5 Comments
finnTheHumin
@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.
mmmmmm
@finnTheHuman The question asks how to " copy data from all records" which the earlier answer answers correctly so this does not add anything
finnTheHumin
@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
luckydonald
In my case I added a last_seen column.
UPDATE user SET last_seen = first_seen WHERE last_seen IS NULL
Nick T
...but I need a clause in mine that limits it to 100 records /s
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;
Comments
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.
answered Nov 11, 2021 at 19:57
Muhammad Dyas Yaskur
8,34811 gold badges62 silver badges91 bronze badges
Comments
On same systems, like BigQuery you might need to add a where clause, therefore:
update table set target = source where 1=1;
