Update a Table Inner Join with onother table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update a Table Inner Join with onother table

Author

Topic

Naveed88
Starting Member

19 Posts

Posted - 2009-04-13 : 06:54:59

Hi all,

I want to Update few records in a table with another table's values
by Inner join

Table1
ID Name Amount
1 AAA 100
2 BBB 200
3 CCC 300
4 DDD 400

and Another one is
Table2
ID Discount
1 50
2 60
4 80

what i want to update all Table1.Amount
by Table2

like
Subtract all discount from amounts in Table1 which are Exist in
Table2

-----
also i tried this

UPDATE DISTINCTROW table1 t1
INNER JOIN table2 t2 ON t1.code=t2.code
SET t1.name=T2.name where <condition>
---
UPDATE [Table1] ;
SET [Table1.SomeField] = [Table2.SomeField] ;
FROM [Table1] INNER JOIN [Table2] ;
ON [Table1.key]= [Table2.key]

but it does not work!!!

(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer,9867374437-Mumbai.4

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-13 : 07:08:31

is this u want

declare @Table1 table(ID int, Name varchar(12), Amount decimal(18,2))
insert into @table1 select
1, 'AAA', 100 union all select
2, 'BBB', 200 union all select
3, 'CCC', 300 union all select
4, 'DDD', 400

declare @Table2 table(ID int, Discount decimal(18,2))
insert into @table2 select
1, 50 union all select
2, 60 union all select
4, 80

select * from @table1
select * from @table2

update t
set amount = amount-discount
from @table1 t
inner join @table2 s on s.id = t.id

select * from @table1

Naveed88
Starting Member

19 Posts

Posted - 2009-04-13 : 07:21:44

Thanks
that's it
Problem Solved

(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer,9867374437-Mumbai.4

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-13 : 07:24:18

quote:
Originally posted by Naveed88

Thanks
that's it
Problem Solved

(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer,9867374437-Mumbai.4

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources