need SQL help - SQL Server Forums

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

Transact-SQL (2000)

need SQL help

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2005-11-04 : 00:24:35

I have a query to update the existing rows in SQL table.

insert into ffx_Recon (Waehrung,Currency,KAGBuyAmount,KAGSellAmount, KAGTradeDate,KAGSettleDate,Broker,RegID)
SELECT k.Currency,k.Currency,k.KAGBuyAmount,k.KAGSellAmount,
k.KAGTradeDate,k.KAGSettleDate ,k.Broker, 146
FROM ffx_KAG k left join ffx_Recon r on r.Currency = k.Currency
WHERE k.RegID = 146
AND k.RegID = r.RegID And
r.SSBBuyAmount = k.KAGBuyAmount AND r.SSBSellAmount = k.KAGSellAmount AND
r.SSBTradeDate = k.KAGTradeDate AND r.SSBSettleDate = k.KAGSettleDate
AND r.KAGBuyAmount = 0 AND r.KAGSellAmount = 0 AND r.KAGTradeDate Is Null AND r.KAGSettleDate Is Null

This is not working properly. This query is called from VB application. The select query return me the correct records that needs to be updated. But the update part is not working.

What I am actually doing is comparing ffxRecon and ffxKAG tables and update the matched records in ffxRecon Table with the values from ffxKAG Table.

Do I need to use UPDATE instead of INSERT INTO? I am not sure UPDATE works when we need to update multiple fileds...

shubhada

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-04 : 00:48:49

See if you need something like this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=57225

Madhivanan

Failing to plan is Planning to fail

shubhada
Posting Yak Master

117 Posts

Posted - 2005-11-04 : 00:59:24

but suppose both tables have 5 columns.and i found that out 5 column,3 columns have differnt values then how I can set those 3 values from one tables values to other using update query.

shubhada

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-04 : 01:01:47

Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

shubhada
Posting Yak Master

117 Posts

Posted - 2005-11-04 : 01:22:38

table1:
col1 col2 col3 col4 col5
1 a 2 3 4
2 b 2 5 6
3 c 3 4 6

table2:
col1 col2 col3 col4 col5
1 a 2 5 7
2 b 1 5 8
3 c 5 4 6
4 d 2 3 5

So finally table1 should be
col1 col2 col3 col4 col5
1 a 2 5 7
2 b 1 5 8
3 c 5 4 6

shubhada

shubhada
Posting Yak Master

117 Posts

Posted - 2005-11-04 : 02:09:20

I tried one logic using the same example which is avaliable on above link .
and now it is works fine .
thanks a lot

shubhada

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources