Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

UPDATE from a SELECT

Hi,

I want to create T-SQL stored procedure that will do the following task :

I have 2 tables. A and B. A and B have 4 fields in common. The fields are named F1, F2, F3 and F4. B is the reference table that contain an ID (say K1 as the field name) and the 4 fields (Fx). A is the data table that contains both the foreign key K1 and also the fields (Fx). So basically, I can do a select on table A and get by using relatinal data the related value of F1, F2, F3 and F4 using the foreign key of A.K1 and primary key of B.K1. But B is so big (millions records), I want to remove the relational for specific queries that has to be very fast and a so select on one table only (A). So periodically, I want to execute a stored procedure that will take the data from B and put them in A using both a SELECT (to get the related data) and an UPDATE (to update the table A).

Look this pseudo code, I want this in TSQL

ARECORDSTOBEUPDATED = SELECT ALL RECORDS FROM A THAT DO NOT CONTAIN F1 VALUE (this means we did not normalized it yet)

( FOREACH MAINDATAROW IN ARECORDSTOBEUPDATED DO:

  REFERENCEROW =  SELECT TOP 1 FROM B WHERE B.K1 = MAINDATAROW .K1

 UPDATE MAINDATAROW WITH REFERENCEROW DATA (F1, F2, F3, F4)
)

This is quite simple, but I really don't know how to achieve this.

Thanks a lot in advance.
0
pmengal
Asked:
pmengal
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
update a
  SET F1 = B.F1, F2 = B.F2, F3 = B.F3, F4 = B.F4
from A
join B
  on A.K1 = B.K1
 and A.F1 IS NULL
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now