UPDATE from a SELECT
Posted on 2006-11-21
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.