Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CrazyT
Yak Posting Veteran
73 Posts |
 Posted - 2010-04-29 : 12:01:18
|
| I have a situation where i need to update one table from a select on another table. I then need to update a flag on the select table as completed.only thing i can think to do is select my data into a temp table and update both tables with updates from joins on that and then do a db commit.is there a better way to do this? |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-04-29 : 12:02:48
|
| I don't think that temp table is required for update.Can you give some sample data..I am here to learn from Masters and help new bees in learning. |
 |
|
|
CrazyT
Yak Posting Veteran
73 Posts |
Posted - 2010-04-29 : 12:06:20
|
| very basic tables table 1tableid, transID int, columns1, columns2table 2table2id, transid, columns1new, columns2new, updatedmaintablebasically i need to move the values from table 2 columns1new, columns2new into table 1 columns1, columns2 on matching transid. then update table 2 updatedmaintable = 1 |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-04-29 : 12:56:29
|
| [code]Update t1 set t1.columns1=t2.columns1new,t1.columns2=t2.columns2new,t2.updatedmaintable=1 from table1 t1 inner join table2 t2 on t1.transid=t2.transid[/code]PBUH |
|
|
|
CrazyT
Yak Posting Veteran
73 Posts |
Posted - 2010-04-29 : 13:10:35
|
| i get cant identify multipart identifier on the t2.updatedmaintable=1 |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-04-29 : 13:26:10
|
| Oh sorry thats not possible.You cannot update 2 tables in one update statement.PBUH |
|
|
|
CrazyT
Yak Posting Veteran
73 Posts |
Posted - 2010-04-29 : 14:04:04
|
| thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-29 : 14:15:34
|
I would use a TEMP table, but its possible this could be done using OUTPUT statement.My reason for using a TEMP table would bea) to ensure that the actual records participating in the two updates did not change between Update1 and Update 2  b) TO optimise the updates by storing just the PK fields in the TEMP table(and c) to double check that the number of records considered in both updates was the same - i.e. to ROLLBACK if the underlying data had changed in any way between Update1 and Update2) |
|
|
|
|
|
|
|
|
best way to update one table and flag another