Copy data from one existing row to another existing row in SQL?

sql

subqueries

joins

data-integrity

byAlex Kataev·Oct 25, 2024

To quickly copy data between rows, you can use UPDATE together with a SELECT in SQL:

UPDATE my_table
SET column_a = (SELECT column_a FROM my_table WHERE id = source_row_id),
    column_b = (SELECT column_b FROM my_table WHERE id = source_row_id)
WHERE id = target_row_id;

Here, just replace my_table with your real table name, column_a and column_b with the columns you intend to copy, and source_row_id and target_row_id with your specific row identifiers. This way, you replicate specific columns swiftly, leaving the rest of the data untouched.

Taking aim: conditional updating

Sometimes you need to update specific records. To do this accurately, it's important to set clear conditions using the WHERE clause. Let's say: You only want to update user data for those who pulled an all-nighter and finished a course after a certain date. Your code might look like this:

// No coffee no workee ☕
UPDATE user_data
SET user_score = (SELECT user_score FROM user_data WHERE user_id = source_user_id AND course_id = 6 AND completion_date > '2008-08-01'),
    completion_date = (SELECT completion_date FROM user_data WHERE user_id = source_user_id AND course_id = 6 AND completion_date > '2008-08-01')
WHERE user_id = target_user_id AND course_id = 11;

Here, subqueries let us filter the source data and transfer only relevant records, avoiding unneeded updates. Sharpshooter precision!

Advanced maneuver: complex updates

Sometimes, data between rows doesn't have a direct correlation. But fear not, JOINs or sub-selects to the rescue! These offer you unprecedented efficiency in complex data handling. Let's use a role update operation as an example:

// Elevating you from "Bob in HR" to "Bob, CEO of the break room" 🎖️
UPDATE users u
SET u.role_id = roles_map.new_role_id
FROM (SELECT old_role_id, new_role_id FROM roles_change_map) roles_map
WHERE u.role_id = roles_map.old_role_id;

The roles_map sub-table provides a dynamic relationship mapping between old and new roles, ensuring your operation is as smooth as melted butter.

Hold the door: maintaining data integrity

When working with user data, especially in applications with more users than a sold-out concert, data integrity is your guardian angel. We match records based on a unique user_id, as seen here:

// Oops! I accidentally dropped my join...  Oh, it was an INNER JOIN. Thank god I didn't OUTER myself 😅
UPDATE user_stats
SET completed_tasks = (SELECT completed_tasks FROM user_stats WHERE user_id = 43)
WHERE user_id = 87;

This pretty sensitive stuff that should be followed by checks to verify your update is both complete and accurate. Otherwise, you might open a Pandora’s Box filled with data corruption.

Supercharging SQL: boosting performance on large datasets

Working with large datasets or performing regular operations requires some finesse. Techniques like subquery optimizations, index usage, and avoiding table scans like vampires avoid sunlight can boost your time-efficiency.

UPDATE large_table t1
SET column_c = t2.column_c
FROM (SELECT id, column_c FROM large_table WHERE condition) t2
WHERE t1.id = t2.id
AND t1.another_condition;
// Much fast, very wow, so SQL! 🐕‍🦺