根据另一个表中的所有值作为参数更新表 [英] Update table based on all values in another table as parameters

问题描述

Table1 Column1 Value1 Table2 Column2 Value2-------------------------------------------------------------------tbl_start DESC 蓝色 tbl_end 描述 红色tbl_job 工作医生 tbl_role 工作外科医生

我正在尝试创建一个 SQL 存储过程，它将根据映射表更新表(我已经创建了上面的示例)

我尝试了一些变体，但似乎无法理解如何编写查询

例如:

UPDATE @TABLE2 SET @Column2 = @Value2 where @Value2 = @Value1

所以说 tbl_end 在 Description 列中有值 'blue'，在存储过程运行后，它应该将所有 'blue' 实例更新为 'red'

同样，如果 tbl_role 在 JOB 列中的值为Doctor"，则在存储过程运行后，它应该将Doctor"更改为Surgeon"

感谢您的任何帮助、建议或意见

感谢任何反馈

(在这种情况下不担心安全风险)

解决方案

UPDATE 和 JOIN 语句的经典案例:

更新表1SET Table2.Value2 = Table1.Value1FROM Table1 JOIN Table2 ON Table1.Column1 = Table2.Column2

查看此链接了解更多详情:

使用连接的 SQL 更新查询 ,如何使用 JOIN 执行 UPDATE 语句SQL?

如果您想更新多个表 - 因为您不能在一个语句中更新多个表 - 您必须针对您希望更新的每个表运行此代码..>

Table1     Column1     Value1     Table2     Column2        Value2
-------------------------------------------------------------------
tbl_start   DESC        blue      tbl_end     Description     red
tbl_job     JOB         Doctor    tbl_role    JOB             Surgeon

I am trying to create an SQL stored procedure which will update tables based on the mapping table (I have created the one above as an example)

I have tried a few variations but can't seem to get my head around how the query will be written

For example:

UPDATE @TABLE2 SET @Column2 = @Value2 Where @Value2 = @Value1

So say tbl_end had the value 'blue' in the Description column, after the stored procedure runs it should have updated all instances of 'blue' to 'red'

In the same way if tbl_role had the value 'Doctor' in the JOB column, after the store procedure runs it should have changed 'Doctor' to 'Surgeon'

Thank you for any help, suggestions or comments

I appreciate any feedback

(Not worrying about security risks in this instance)

解决方案

It's a classic case of UPDATE with JOIN statement:

UPDATE Table1
SET Table2.Value2 = Table1.Value1
FROM Table1 JOIN Table2 ON Table1.Column1 = Table2.Column2

See this links for more details:

SQL update query using joins , How can I do an UPDATE statement with JOIN in SQL?

Edit:

If you want to update multiple tables- since you can't update more that one table in a one statement - You will have to run this code against every table you wish to update.

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