我如何从一个表更新到另一个表 [英] How I Update From One Table To Another Table

问题描述

我需要从另一个temp_table更新一个表（主表）





table1（主表）



billno。姓名地址日期有趣

A1001 [null] [null] [null] ABC

B1002 [null] [null] [null] BCA

A1004 [null] [null] [null] BAC





table2（临时表）



名称地址date fun billno。

qqq wwww 10/11/11 ABC A1001

wee tttt 21/10/12 BCA B1002

trrr oooo 11/09/21 BAC A1004



i有两个常见字段fun n billno。

从TEMPtable到MAIN的更新表。

现在我可以更新主表的名称地址日期。

和条件是我想只更新'A100'





我可以看到值



select * from temp_table其中billno喜欢'A100％'< br $>




但是我需要在主表中仅更新A100行的临时表格



table1（主表）OUTPUT



billno。姓名地址日期乐趣

A1001 qqq wwww 10/11/11 ABC

B1002 [null] [null] [null] BCA

A1004 trrr oooo 11/09/21 BAC

解决方案

您可以使用触发器概念并使用魔术表，但触发器不是很好的练习，您也可以尝试不同的方法



你可以去下面的链接



http://stackoverflow.com/questions/12137102/sql-update-trigger-only-when-column-is-modified [ ^ ]



触发器 - SQL Server [ ^ ]

试试这个：

  UPDATE  t1  SET  t1。[name] = t2。[name]，t1.address = t2.address，t1 。[ date ] = t2。[ date ] 
  FROM  table1  as  t1  INNER  加入 table2  AS  t2  ON  t1.billno = t2.billno 
  WHERE  t1.billno 赞 '  A100％' 

i need to update one table(main table) from a another temp_table


table1(main table)

billno. name address date fun
A1001 [null] [null] [null] ABC
B1002 [null] [null] [null] BCA
A1004 [null] [null] [null] BAC


table2(temp table)

name address date fun billno.
qqq wwww 10/11/11 ABC A1001
wee tttt 21/10/12 BCA B1002
trrr oooo 11/09/21 BAC A1004

i have two common fields fun n billno.
UPDATE from TEMPtable to MAIN table.
now i can to update name address date of main table .
and condition is i want to update only 'A100'


for that i can see the values

select * from temp_table where billno like 'A100%'


but i need to update only A100 rows in main table from temptable

table1(main table) OUTPUT

billno. name address date fun
A1001 qqq wwww 10/11/11 ABC
B1002 [null] [null] [null] BCA
A1004 trrr oooo 11/09/21 BAC

解决方案

You may use trigger concept and play with magic table,however trigger is not good practice,you may attemp different approach also

you may go to below link

http://stackoverflow.com/questions/12137102/sql-update-trigger-only-when-column-is-modified[^]

Triggers -- SQL Server[^]

Try this:

UPDATE t1 SET t1.[name] = t2.[name], t1.address = t2.address, t1.[date] = t2.[date]
FROM table1 as t1 INNER JOIN table2 AS t2 ON t1.billno = t2.billno
WHERE t1.billno Like 'A100%'

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