Solved

SELECT INSERT INTO OR UPDATE

Posted on 2010-09-22
3
439 Views
Last Modified: 2012-05-10
Hi!

I have a database where i have the stock balance... This i want into a new table and want to update the field or add the field depend if the record exsists....

THE TABLE I WANT TO ADD OR UPDATE DATA TO
SELECT PK, ProdNo, Val1, Val2, Val3 FROM f9000.dbo.FreeInf1

THE TABLE I ASK FROM
SELECT DISTINCT Prod.ProdNo,
      (SELECT SUM(LAGER.Bal + LAGER.stcinc - LAGER.shprsv - LAGER.PicNotR) as Lagersaldohovedlager FROM STCBAL as LAGER WHERE LAGER.Prodno=Prod.ProdNo AND stcNO='1') AS Val1,
      (SELECT SUM(stcbal.InPurc) as IBestilling FROM STCBAL WHERE prod.prodno=stcbal.prodno) AS Val2,
      (SELECT SUM(stcbal.InO) as IOrdre FROM STCBAL WHERE prod.prodno=stcbal.prodno) AS Val3
      --INSERT INTO f9000.dbo.FreeInf1 (ProdNo,Val1,Val2,Val3) VALUES (ProdNo,Prod.LagersaldoHovedlager,Prod.IBestilling,Prod.IOrdre)
FROM F0003.dbo.Prod
WHERE (ProdPrGr='29' AND ProdPrG2='3' AND ProdPrG3='4')
0
Comment
Question by:myhrvold
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33733960
what data base product are you using?

unless you are using MySQL, SQL 2008+ or Oracle (possibly others), where you have some "MERGE" syntax, you have to do this with 2 statements.
which would be usually a UPDATE, and if that affected no records, doing a INSERT  ...
0
 
LVL 7

Accepted Solution

by:
mmr159 earned 500 total points
ID: 33734103
If I understand the issue correct, you want to update a record if it exists, otherwise insert a new one... correct?  Here is the syntax that will update or insert, depending on of it exists already.

/*
If record exists in table xyz where x = 99, update y = 77,
otherwise, insert the new record
*/
IF EXISTS (
	SELECT *
	FROM xyz
	WHERE x = 99
	)
BEGIN
	UPDATE xyz SET y = 77
	WHERE x = 99
END
ELSE
BEGIN
	INSERT INTO xyz (x,y,z)
	SELECT 99,77,88
END

Open in new window

0
 

Author Comment

by:myhrvold
ID: 33734146
The database is MS SQL Server 2005
0

Featured Post

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question