如何将与MainUrl关联的Url行的Id填入对应行的MainId列？

Got it, let's break this down for you. Even though you know this approach isn't the most efficient or standard, I'll show you how to get it done for both SQL databases and spreadsheets (since you didn't specify which tool you're using).

1. 如果你用的是SQL数据库（比如MySQL、PostgreSQL、SQL Server） #

First, let's assume your table is named url_table with columns: Id, Url, MainUrl, MainId. The goal is to fill MainId with the Id of the row where Url matches the current row's MainUrl.

You can use a self-join UPDATE query to achieve this. Here's the code tailored to common databases:

-- For MySQL/MariaDB
UPDATE url_table t1
INNER JOIN url_table t2 
  ON t1.MainUrl = t2.Url
SET t1.MainId = t2.Id;

-- For PostgreSQL (uses a slightly different UPDATE syntax)
UPDATE url_table t1
SET MainId = t2.Id
FROM url_table t2
WHERE t1.MainUrl = t2.Url;

-- For SQL Server
UPDATE t1
SET t1.MainId = t2.Id
FROM url_table t1
INNER JOIN url_table t2 
  ON t1.MainUrl = t2.Url;

关键说明： #

• We're joining the table to itself (t1 is the row we want to update, t2 is the row with the matching Url).
• If multiple rows have the same Url matching a MainUrl, the query will use the last matching Id (behavior varies slightly by database—if you need to handle duplicates, you might add an aggregate like MAX(t2.Id) or MIN(t2.Id)).
• Rows where MainUrl has no matching Url will keep their MainId as NULL (you can switch to LEFT JOIN if you want to set a default value instead).

2. 如果你用的是Excel/Google Sheets #

If you're working with a spreadsheet, you can use lookup functions to pull the matching Id directly.

Using VLOOKUP: #

In the first cell of your MainId column (e.g., cell D2 if MainUrl is in column C), enter this formula:

=VLOOKUP(C2, $A$2:$B$1000, 1, FALSE)

Then drag the formula down to apply it to all rows.

Using XLOOKUP (more intuitive for newer Excel versions): #

=XLOOKUP(C2, $B$2:$B$1000, $A$2:$A$1000, "No match")

关键说明： #

• $A$2:$B$1000 refers to the range containing your Id (column A) and Url (column B) — adjust the range to match your actual data size.
• FALSE in VLOOKUP ensures an exact match; "No match" in XLOOKUP is a fallback if no matching Url is found (you can replace this with "" or another value as needed).

内容的提问来源于stack exchange，提问作者Denis Evseev