• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

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
0
rgb192
Asked:
rgb192
5 Solutions
 
lwadwellCommented:
Where does 'table1.varcharColumn1' come from?
0
 
selva_konguCommented:
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
 
lwadwellCommented:
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
fourckCommented:
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]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
 
keyuCommented:
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
 
rgb192Author Commented:
thanks for the if else

if I was using 2008, I would have merge
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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