[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

update row where productid=204 or insert row with productid=204

Posted on 2012-09-04
5
Medium Priority
?
469 Views
Last Modified: 2012-09-08
want a query to insert (if productid is not there)
or update (if productid is there)

UPDATE testcategory SET categoryname = 'electronics'
WHERE productid = 204;

INSERT INTO testcategory (productid,categoryname)
SELECT 204, 'electronics'


testcategory table
productid int
cateogoryname varchar
0
Comment
Question by:rgb192
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38365173
in sql 2005, you would do this:

UPDATE testcategory SET categoryname = 'electronics'
WHERE productid = 204;

IF @@ROWCOUNT = 0
BEGIN
 INSERT INTO testcategory (productid,categoryname)
 SELECT 204, 'electronics'
END 

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38365180
Pick your id, shove it into a variable, and use EXISTS to test...

Declare @productid int = 204

IF EXISTS (SELECT ProductID FROM testcategory WHERE productid = @productid )
   begin
   UPDATE testcategory
   SET categoryname = 'electronics'
   WHERE productid = @productid
   end
ELSE
   begin
    INSERT INTO testcategory (productid,categoryname)
    VALUES @productid, 'electronics'
   end
0
 
LVL 9

Expert Comment

by:keyu
ID: 38367313
See below code might work in sql 2005 its working for me in sql 2008

MERGE BookInventory bi
 
USING BookOrder bo
 
ON bi.TitleID = bo.TitleID
 
WHEN MATCHED AND
 
  bi.Quantity + bo.Quantity = 0 THEN
 
  DELETE
 
WHEN MATCHED THEN
 
  UPDATE
 
  SET bi.Quantity = bi.Quantity + bo.Quantity
 
WHEN NOT MATCHED BY TARGET THEN
 
  INSERT (TitleID, Title, Quantity)
 
  VALUES (bo.TitleID, bo.Title,bo.Quantity);

for more details use below link..might helps you...

http://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/
 
 
 
SELECT * FROM BookInventory;
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38367339
unfortunately, the MERGE is not available in SQL 2005
0
 

Author Closing Comment

by:rgb192
ID: 38380185
this is the only query that worked for me

I got decare errors and merge error with others


thanks
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

873 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