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

Basic data type considerations

Your UPDATE will be seamless generally, but when dealing with mismatched data types, things can get a bit dodgy. If you're using PostgreSQL, you can remedy this with data type conversions or 'casts', declared with ::TIMESTAMP or ::INTEGER.

-- We're fitting square pegs in round holes here
UPDATE YourTableName
SET TargetColumnName = SourceColumnName::INTEGER;


Neatly, if you're moving data into a character varying column, the casting parade isn't needed. It's basically happy with anything.

Conditional copying with a WHERE clause

The initial statement we gave updates every single row, but hey, maybe you have something a little more suspenseful in mind? Use the WHERE clause to target your updates with surgical precision:

-- Not everyone gets to join the party
UPDATE YourTableName
SET TargetColumnName = SourceColumnName
WHERE SpecificCondition;


Surviving in safe mode

Some SQL environments are paranoid and won't allow updates without a WHERE clause. But, like a MacGyver of SQL, you can craft a condition good enough to lull it into cooperation:

-- See? I'm not threatening, I'm friendly!
UPDATE YourTableName
SET TargetColumnName = SourceColumnName
WHERE PrimaryKeyColumn > 0;


Outplaying the exceptions

Handling NULLs and defaults

Null values are like party crashers, they just show up uninvited. But throw a COALESCE in your query, you can pretend they're just friends you haven't met:

-- Stranger danger? Not here
UPDATE YourTableName
SET TargetColumnName = COALESCE(SourceColumnName, 'Default Value');


Feeding off complex data

When the data feeds on multiple columns or bears the weight of heavy calculations, soaking the horsepower of subqueries or concatenation can be your lifesaver:

-- Time to level up!
UPDATE YourTableName
SET TargetColumnName = (SELECT ComplexCalculation FROM … WHERE …);
-- or...
UPDATE YourTableName
SET TargetColumnName = ColumnOne || ' ' || ColumnTwo;


Coping with large datasets

Working with voluminous tables is like trying to solve a Rubik's cube underwater. Here's a neat trick to hold your breath - update the table in chunks to dodge the heavyweight champ, 'resource exhaustion':

BEGIN;
-- Slow and steady wins the race, right?
UPDATE YourTableName
SET TargetColumnName = SourceColumnName
WHERE id BETWEEN 1 AND 50000;
COMMIT;


Repeat with different ranges until your table transforms completely!