how to update from 1 table to another

shabeer shaik

13y1.8kSQL

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Choudhary13y ago
Accepted 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 Stewart13y ago
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 shaik13y ago
THANKS AMIT

It works like a gift. Thank you
shabeer shaik13y ago
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 Choudhary13y ago
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.