INSERT与SELECT语句使用方法及同表跨行列数据迁移求助

Hey there! Let's break down how to solve your problem—moving the lat and long values from the row with Question_id=146 to the row with Question_id=142 in the same table.

First, a quick clarification: since you're updating an existing row (not inserting a new one), we'll use an UPDATE statement paired with a subquery or join to fetch the values from row 146. Here are two reliable approaches:

Approach 1: Using Subqueries #

This is straightforward and easy to read. We directly target the row to update, then pull the needed values via subqueries:

UPDATE your_table_name
SET lat = (SELECT lat FROM your_table_name WHERE Question_id = 146),
    long = (SELECT long FROM your_table_name WHERE Question_id = 146)
WHERE Question_id = 142;

• Replace your_table_name with the actual name of your table.
• The WHERE clause ensures we only modify the row with Question_id=142.
• The subqueries inside SET grab the lat and long values from row 146.

Approach 2: Using a Self-Join (More Efficient) #

If you're working with large datasets, a self-join is often faster than subqueries. This method also avoids accidentally setting lat/long to NULL if row 146 doesn't exist:

UPDATE your_table_name t1
JOIN your_table_name t2 ON t2.Question_id = 146
SET t1.lat = t2.lat,
    t1.long = t2.long
WHERE t1.Question_id = 142;

• We alias the table as t1 (the row we want to update) and t2 (the row with the values we need).
• The JOIN ensures we only perform the update if row 146 exists.

Important Pre-Check #

Before running any update, it's smart to verify the values you're about to set. Use this SELECT statement to preview the changes:

SELECT 
    t1.Question_id,
    t1.lat AS original_lat,
    t2.lat AS new_lat,
    t1.long AS original_long,
    t2.long AS new_long
FROM your_table_name t1
JOIN your_table_name t2 ON t2.Question_id = 146
WHERE t1.Question_id = 142;

This will show you the current lat/long values for row 142 alongside the values from row 146, so you can confirm everything looks right before making changes.

Why Not INSERT? #

Just to clarify: INSERT is used to add new rows to a table. Since you're modifying an existing row (row 142), UPDATE is the correct statement here.

内容的提问来源于stack exchange，提问作者Mohammad Ahmad Shabbir