how to update from 1 table to another

Question

hallo Vulpes

I have a table in sql called tb1
the contents is something like this:
crse dist wintime winweight
cd 1400 67.6 60
cd 1600 71.2 58
sv 1400 69.0 59

in tbl2 in sql
crse dist partime winweight
cd 1400
cd 1600
sv 1400

I WANT TO AUTOMATICALLY UPDATE TBL2 PARTIME,WINWEIGHT WITH WINTIME ,WINWEIGHT FROM TBL1 WHERE CRSE AND DIST ARE THE SAME.

Amit Choudhary · Answer

Since we have two columns in the where condition so it can be considered as composite key. In the sample that you're giving is unique from the composite key aspects. so you should be good to go with the query. The composite key is when we consider two columns data together as a unique key. For you question regarding top 1 The top 1 is actually taking first records from the result set returned by select statement. Hope this was helpful. :)

Sean Stewart · Answer

I hope this blog will help you http://decipherinfosys.wordpress.com/2007/01/31/update-data-in-one-table-with-data-from-another-table/

shabeer shaik · Answer

THANKS AMIT It works like a gift. Thank you

shabeer shaik · Answer

hallo Amit thanks for helping me out. table 1 has a lot of duplicates example CD 1200 CD 1400 GE 1200 GE 1400 SR 1200 SR 1400 table 1 never changes. table 2 is new data everyday. what is top1

Amit Choudhary · Answer

Allow me to entertain you my friend since our friend Vulpes must be busy with other stuff. :) Try this query: UPDATE tbl2 SET tbl2.partime = ( SELECT top 1 wintime FROM tbl1 WHERE tbl1.crse = tbl2.crse AND tbl1.dist = tbl2.dist ) tb2.winweight = ( SELECT top 1 winweight FROM tbl1 WHERE tbl1.crse = tbl2.crse AND tbl1.dist = tbl2.dist ) We don't need top 1 I just used it as precaution in case we have duplicates for both columns crse and dist.