if value is not in table, insert, else update

table2
varcharColumn1
varcharColumn2

one command
insert into table2 (varcharColumn1, varcharColumn2) values (table1.varcharColumn1,'9')

or if table1.varcharColumn1 is in table2.varcharColumn1 then update table2 set varcharColumn2='9' where table2.varcharColumn1=table1.varcharColumn1
LVL 1
rgb192Asked:
Who is Participating?
 
keyuConnect With a Mentor Commented:
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
 
lwadwellCommented:
Where does 'table1.varcharColumn1' come from?
0
 
selva_konguConnect With a Mentor Commented:
IF exists(select t1.varcharColumn1 from table1 t1 inner join table2 t2 on t1.varcharColumn1 = t2.varcharColumn1)
begin
update table2 set varcharcolumn2 = 9
end

else
begin
insert into table2 (varcharColumn1, varcharColumn2) values (table1.varcharColumn1,'9')
end

-- selvas--
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
lwadwellConnect With a Mentor Commented:
Need to understand where 'table1.varcharColumn1' comes from.  The syntax
    INSERT INTO tablename (col1,col2) VALUES (values1,value2)
does apply when dealing with table columns ... you need to do
    INSERT INTO tablename (col1,col2) SELECT col1, col2 FROM othertable

If you are inserting from another table ... need to understand how many rows are being selected to be inserted/updated.  The example provided by @selva_kongu above would work well if only one row exists in both table1 and table2 ... any more than that and the logic will go wrong as
a) update table2 set varcharcolumn2 = 9 ... will update every record in table2, not just those that match table1 on varcharColumn1
b) the EXISTS in the IF will succeed on the first record found ... if there were records to be INSERTed and some to be UPDATEd ... then the INSERT is skipped.

For multiple rows from table1 into table2 ... the likely logic - as two steps (in this order) - might be:
UPDATE table2 SET varcharcolumn2 = 9
WHERE EXISTS (SELECT 1 FROM table1 t1 WHERE t1.varcharColumn1 = table2.varcharColumn1);

INSERT INTO table2 (varcharColumn1, varcharColumn2)
SELECT varcharColumn1, '9'
FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.varcharColumn1 = table1.varcharColumn1);

Open in new window

But is still depends on the answer to the question "Where does 'table1.varcharColumn1' come from?".

There in no easy 1 step method in 2005 ... as of version 2008 the MERGE command was available.  The complex method in version 2005 would be via a VIEW and an INSTEAD OF TRIGGER on the VIEW - if this was to be a regular process, I might recommend it.
0
 
fourckConnect With a Mentor Commented:
Use (IF EXISTS) and (NOT EXISTS),  there is many tutorial on net that could help you.

Good luck!
0
 
rgb192Author Commented:
>>
If you are inserting from another table ... need to understand how many rows are being selected to be inserted/updated


only one field
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
as indicated above, what you want to do is a MERGE ...
but with sql 2005, not available directly, you need 2 statements:
1 to update, and after that update, checking if the update actually happened (rows affected), and if no rows updated, to perform the insert
http://sqlserver-tips.blogspot.com/2006/09/mimicking-merge-statement-in-sql.html
0
 
rgb192Author Commented:
thanks for the if else

if I was using 2008, I would have merge
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.