How to update and insert a table in the same query

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 2005 Forums

Transact-SQL (2005)

How to update and insert a table in the same query

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-06 : 13:59:27

Hello All,

I have an Update statment that update the current_Value table setting vaule = 0, if record_id is not found in the pervious_Value table. Also update value = 1 where record_id from table_a and table_B are equal. Now I need an insert statement that inserts records that is not found in the current_value table but exsist in the pervoius_value table and changing the sign on the amount field if not zero. In example of the tables and result table is shown below:

Pervious_Value
Record_ID First_Name Amount Value Difference
1 Mike 50 25 NULL
2 Sue 100 45 NULL
3 Chris 75 15 NULL
4 Tom 115 125 NULL
9 Cliff 20 20 NULL

Current_Value
Record_ID First_Name Amount Value Difference
5 Brent 5 25 NULL
6 Kim 30 30 NULL
1 Mike 50 25 NULL
2 Sue 100 45 NULL
3 Chris 75 15 NULL
4 Tom 115 125 NULL

Results
Record_ID First_Name Amount Value Difference
5 Brent 5 0 NULL
6 Kim 30 0 NULL
1 Mike 50 1 NULL
2 Sue 100 1 NULL
3 Chris 75 1 NULL
4 Tom 115 1 NULL
9 Cliff 20 -20' NULL

UPDATE b
SET b.Value=(CASE WHEN a.Record_ID IS NULL THEN 0 ELSE 1 END )
FROM Current_Value b
LEFT JOIN pervious_Value a
ON a.Record_ID=b.Record_ID

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 14:46:08

sql only allows for one operation per statement. Either (select, insert, update, or delete). So Updates and Inserts needs to be seperate statements.

Be One with the Optimizer
TG

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-06 : 15:59:59

quote:
Originally posted by TG

sql only allows for one operation per statement. Either (select, insert, update, or delete). So Updates and Inserts needs to be seperate statements.

Be One with the Optimizer
TG

not really, check MERGE and OUTPUT INTO

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-10-06 : 16:38:52

Yes, the OUTPUT is something I recently discovered and it is great.

Could you also use a Trigger?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 16:45:21

Thanks - I stand corrected

I have yet to implement a MERGE solution - how's the performance vs. update/insert? I would guess that even though the user code is one statement it ends up being resolved into seperate ops under the sheets - but that is just a guess...

Be One with the Optimizer
TG

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-06 : 16:54:26

even I havn't tried :) my company doesn't want to invest in 2008 yet.

smarty
Starting Member

13 Posts

Posted - 2009-10-07 : 06:39:21

quote:
Originally posted by TG

Thanks - I stand corrected

I have yet to implement a MERGE solution - how's the performance vs. update/insert? I would guess that even though the user code is one statement it ends up being resolved into seperate ops under the sheets - but that is just a guess...

Be One with the Optimizer
TG

The MERGE command is SQL2008 only

-----------------------------------
Free SQL server monitoring for DBA's
www.realsmartsoftware.co.uk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources