Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

SELECT INSERT INTO OR UPDATE

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
myhrvold
Asked:
myhrvold
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
mmr159Commented:
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
 
myhrvoldAuthor Commented:
The database is MS SQL Server 2005
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now