Insert a number from one table to another 1

[istone]

Hi,
I am not sure how to do this.
I have table1 with the following fields

Table1
PlanID PlanName Assets Amount
A ANN 1,500,000



Table2

Plan ID MinAssets MaxAssets Amount
A 1,000,000 2,0000,000 10,000
A 2,000,000 3,000,000 5,000
A 4,000,000 2500

What I would like to do is: take amout 10,000 from table2 and insert it in table1 in field Amount (because The Assets are 1,500,000 which fits the criteria of the first field in Table2)

table1 will look like this:

Table1
PlanID PlanName Assets Amount
A ANN 1,500,000 10,000



Please let me know if this is not clear. Thanks in advance

 

[PWise]

update Table1 set Table1.Amount = Table2.amount
inner join table2
on Table1.planid=Table2.planid
and table1.Assets  between Table2.MinAssets and Table2.MaxAssets

 

[lespaul]

why are you storing it in two places? why not just a query:

SELECT 1.PlanID, 1.PlanName, 1.Assets, 2.Amount
FROM Table1 1
INNER JOIN Table2 2 on 1.Assets BETWEEN 2.MinAssets AND 2.MaxAssets

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[GingerR]

I agree-why store that info? If you change Assets will you then always change AMOUNT?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PWise]

there can times when the amount will not change if table1 is a amount billed you would want to store the amount billed ect.

 

[istone]

thank you. It worked.

 

[PHV]

thank you. It worked
Even with an assets of 5,000,000 ?
I'd use this instead:
UPATE Table1 INNER JOIN table2 ON Table1.planid = Table2.planid
SET Table1.Amount = Table2.amount
WHERE table1.Assets >= Table2.MinAssets
AND (table1.Assets <= Table2.MaxAssets OR Table2.MaxAssets Is Null)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886