earwig75
asked on
Update records in 1 table with results from another
I may not have explained what I am looking for well in the the title, but this is what I am trying to accomplish.
I have results from 1 query (see below), and I want to update all of the records in TableB that have the same ID as the results from the select statement. For example, if the query returns 3 IDs, I want to take those and update the records in TableB based on those IDs. (Update TableB... set somevalue='blah'... etc.
I know this can be done a few different ways in coldfusion or even just using the SQL. I just need a rough example.
Thanks for any help you can offer
I have results from 1 query (see below), and I want to update all of the records in TableB that have the same ID as the results from the select statement. For example, if the query returns 3 IDs, I want to take those and update the records in TableB based on those IDs. (Update TableB... set somevalue='blah'... etc.
I know this can be done a few different ways in coldfusion or even just using the SQL. I just need a rough example.
Thanks for any help you can offer
SELECT TableA.TheID, TableB.AValue, TableA.AUser,
TableB.TheID
FROM TableA INNER JOIN
TableB ON TableA.TheID = TableB.TheID
I would suggest testing it by surrounding it by:
BEGIN TRAN
Select * from TableB
<<Insert Code>>
Select * from TableB
ROLLBACK TRAN
This way you can test if the update has worked without committing the transaction.
BEGIN TRAN
Select * from TableB
<<Insert Code>>
Select * from TableB
ROLLBACK TRAN
This way you can test if the update has worked without committing the transaction.
ASKER
Thank you, I may not have explained myself. I don't want to set items to be equal between the tables.
If my select statement returns IDs: 5,7 and 9, I want to update the records in tableB where the ID is 5,7 and 9...
Something like this:
Update TableB
Set Status="Open", Color="Green"
Where TheID......(this is where i want to use the IDs from the select)
Thank you.
If my select statement returns IDs: 5,7 and 9, I want to update the records in tableB where the ID is 5,7 and 9...
Something like this:
Update TableB
Set Status="Open", Color="Green"
Where TheID......(this is where i want to use the IDs from the select)
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome, that's what I was looking for. Thank you.
SET TableB.AValue = TableA.AUser
FROM TableA
INNER JOIN TableB ON TableA.TheID = TableB.TheID