英文:
I wish to upsert all the column values from my temp_table to master_table in SQL
问题
master_table:
| _id | zipcode |
|---|---|
| 123 | 100 |
| 456 | 200 |
temp_table:
| _id | zipcode |
|---|---|
| 123 | 111 |
| 245 | 222 |
master_table（新结果必须上升到master_table）
| _id | zipcode |
|---|---|
| 123 | 111 |
| 456 | 200 |
我的最终目标是将temp_table中的记录插入到master_table并更新在temp_table中找到的master_table中的现有记录。
我只是提供了一个示例表，需要更新的不仅仅是zipcode列，还有很多列（20+），所有这些列都需要更新。
仅供参考：这两个表具有相同数量的列。
英文:
master_table:
| _id | zipcode |
|---|---|
| 123 | 100 |
| 456 | 200 |
temp_table:
| _id | zipcode |
|---|---|
| 123 | 111 |
| 245 | 222 |
master_table (new results must be upserted in master_table)
| _id | zipcode |
|---|---|
| 123 | 111 |
| 456 | 200 |
my end goal is to insert records from temp_table to master_table and update existing records in master_table found in temp_table.
I have just given a sample table, It is not just the zipcode column which needs to be updated. there are many such columns (20+), all of them need to be updated.
just for a note : both the tables have same no. of columns.
答案1
得分: 1
使用更新连接：
UPDATE master_table AS m
SET zipcode = t.zipcode
FROM temp_table t
WHERE m._id = t._id
英文:
Use an update join:
<!-- language: sql -->
UPDATE master_table AS m
SET zipcode = t.zipcode
FROM temp_table t
WHERE m._id = t._id
答案2
得分: 0
使用 WITH 可以仅获取需要更新的数据。
这种方法允许您开发和测试选择查询，然后在两个步骤中将其转换为更新查询。
with t as (
select m._id, t.zipcode as new_zipcode
from master_table m
inner join temp_table as t on t._id = m._id and t.zipcode <> m.zipcode
)
update master_table m
set zipcode = t.new_zipcode
from t
where m._id = t._id
要更新多列，只需在 inner join 中添加它们，然后在 SET 子句中添加它们：
with t as (
select m._id, t.zipcode, t.column2
from master_table m
inner join temp_table as t on t._id = m._id and (t.zipcode <> m.zipcode or t.column2 <> m.column2)
)
update master_table m
set zipcode = t.zipcode, column2 = t.column2
from t
where m._id = t._id
英文:
You can do it using WITH to get data that need to be updated only.
This approach lets you develop and test your select query and in two steps convert it to the update query.
with t as (
select m._id, t.zipcode as new_zipcode
from master_table m
inner join temp_table as t on t._id = m._id and t.zipcode <> m.zipcode
)
update master_table m
set zipcode = t.new_zipcode
from t
where m._id = t._id
To update multi columns just add them in inner join, and of course add them in the SET clause :
with t as (
select m._id, t.zipcode, t.column2
from master_table m
inner join temp_table as t on t._id = m._id and ( t.zipcode <> m.zipcode or t.column2 <> m.column2)
)
update master_table m
set zipcode = t.zipcode, column2 = t.column2
from t
where m._id = t._id
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论