0

Hi,
I was wondering if it is possible to update one table with another tables values depending on a where condition, such as a insert into or using a join as a subquery.

I have successfully got a windows application to do what I want it to do but it involves a lot of extra work (for the data, not me) and takes a long time

It's simply :

Pseudo Code

string data = //Select required field from 'table1' adding a comma between each result.
string[] split = data.Split(',');
foreach (string record in split)
{
     string newData = //Select required field from 'table2' where fld1 = 'newData'
     //Insert 'newData' into 'table1' where fld1 = 'data'
}

This works but I have 88,000 records so far and each day that'll grow. At the moment this script can do about 2 - 4 records per seconds, so even at the most optimistic ((88000 / 4) * 60) *60) - will take over 6 hours to complete.

I need this to be updated at the very least daily, every 12 hours if possible. Which means this works, for now. After I hit about 175000 it will take more than 12 hours

So as I asked before is there some sort of SQL query that will do the same job but, hopefully, faster

5
Contributors
4
Replies
18
Views
8 Years
Discussion Span
Last Post by mahmoud_wow
-1

HI,
I THINK YOU WANT TO UPDATE A TBALE BASED ON ANOTHER TABLE IN A SINGLE QUERY. IF SO THEN YOU CAN FOLLOW THE MODEL. HERE I AM TRYING TO GIVE YOU AN EXAMPLE ON HOW YOU CAN DO THIS:

update TBL1
set TBL1.COL = TBL2.COL2
from TBL1 inner join TBL2
on TBL1.ID=TBL2.ID
where TBL2.COL >=1

THIS WILL UPDATE ALL ROWS BASED ON WHERE CLAUSE. IT WILL DRAMATICALLY REDUCE YOUR EXECUTION TIME. YOU CAN ALSO CONFIGURE A JOB USING THIS QUERY.

Votes + Comments
caps lock?
0

Thank-you for that! I've been looking all over for how to update a table with values from another. I knew it should be fairly straight forward...

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.