SQL 从另一个表更新一个表 [英] SQL Update a table from another table

问题描述

我是 SQL Server 的完全初学者，我已经达到了我的极限.

I am a complete beginner to SQL Server, and I have reached my limit.

目前我正在使用脚本从另一个使用列的表中更新表.由于两个数据库都分配给了 2 个不同的 3rd 方软件，我创建了一个 .bat 脚本用于 Windows 服务器中的任务管理器，这样它可以每 10 分钟更新一次.

Currently I am using a script to update a table from another table using a column. Since both databases are assigned to 2 different 3rd party software, I created a .bat script to use for task manager in windows server, that way it can update every 10 minutes.

虽然这已经过测试并且有效，但我觉得必须有一种方法可以在不必使用任务的情况下在两个数据库之间创建关系.

While this is tested and works, I feel there has to be a way to create a relationship between the two databases without having to use the task.

UPDATE therefore.dbo.thecat51
SET num_factura = 
 (SELECT therefore.dbo.documentos.num_factura
 FROM therefore.dbo.Documentos
 WHERE therefore.dbo.thecat51.num_albaran=therefore.dbo.documentos.num_albaran)
WHERE therefore.dbo.thecat51.num_albaran = 
 ( SELECT therefore.dbo.documentos.num_albaran
 FROM therefore.dbo.Documentos
 WHERE therefore.dbo.thecat51.num_Albaran = therefore.dbo.documentos.num_albaran)

此外，我们使用的是 SQL Server Express，因此我无法选择创建计划作业.

Also, we are using SQL Server Express, so I don't have the option to create a scheduled job.

推荐答案

在两个表之间使用 INNER JOIN.在我发布这篇文章的时候，你还没有告诉我们你使用的是哪个 RDBMS，所以我会给出 SQL Server 和 MySQL 的答案:

Use an INNER JOIN between your two tables. At the time I posted this, you had not told us which RDBMS you are using so I will give answers for SQL Server and MySQL:

SQL Server:

UPDATE t1 
SET t1.num_factura = t2.num_factura
FROM therefore.dbo.thecat51 AS t1
INNER JOIN therefore.dbo.Documentos AS t2
    ON t1.num_albaran = t2.num_albaran

MySQL:

UPDATE therefore.dbo.thecat51 AS t1
INNER JOIN therefore.dbo.Documentos AS t2
    ON t1.num_albaran = t2.num_albaran
SET t1.num_factura = t2.num_factura

这篇关于SQL 从另一个表更新一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！