Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL statement

Posted on 2013-06-21
18
Medium Priority
?
365 Views
Last Modified: 2013-07-05
Hi,

How can I write statement in MS access to insert a record if not record exists and update record if the record exists in a single statement.

Suppose two records are there in a gridview control

Id   Name
1     abc
2     xyz

and in the table
Id  Name
2   xyz

when I insert/update records into the table, the first record id=1 need to insert and id=2 need to update because it already exists.

Please help.

ayha
0
Comment
Question by:ayha1999
  • 5
  • 4
  • 3
  • +5
18 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 39267493
I doubt you can do that in a SQL statement, because the syntax is so different. You might be able to code the check, and execute one statement or another.
0
 
LVL 7

Author Comment

by:ayha1999
ID: 39267554
can I do it with IIF statment? I tried in the code but not working. I tried both insert and update in single and multiple loops but not working. It was possible in sql server with IF Exists statment. How can I do it in the code?

Thanks
0
 
LVL 53

Expert Comment

by:Gustav Brock
ID: 39267608
You can do that. Usually one table is appending/updating another, so you may have to adopt a little to fit your gridview:

Combined "update or Append" query

/gustav
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
LVL 13

Expert Comment

by:Jitendra Patil
ID: 39267615
you can do it using stored procedures and inside stored procedure
query for id and name using if exists statement.

CREATE PROCEDURE ProceduerName
@yourparameterlist

AS

IF Not Exists(Select Name from tablename where Id=@ID)
 BEGIN
  //Insert statement
 END
ELSE
 BEGIN
   //Update Statement
  END      

hope this helps.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39267633
MERGE statement is what you need...Please read...

http://technet.microsoft.com/en-us/library/bb510625.aspx
0
 
LVL 7

Author Comment

by:ayha1999
ID: 39267638
my db MS Access. can I use stored procedure or MERGE or Append in it? Please give a sample if possible.

Thanks
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39267643
Ooops...I did not realize this is Access since it was posted in the SQL Server section.  Sorry but MERGE can not be used in Access...My guess would be that you need to use VB Access to branch out the code.
0
 
LVL 53

Expert Comment

by:Gustav Brock
ID: 39267654
You can do it with VB(A) as well - a loop and using FindFirst and then either Edit or AddNew.
But SQL (as shown) is much faster in this case.

/gustav
0
 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 400 total points
ID: 39267683
If id in table1 exists in table2 update, otherwise insert in table2

You may try 2 queries:

1. delete table2.* from table2 INNER JOIN table1 on table2.id=table1.id;
2. Insert into table2 select * from table1;
0
 
LVL 53

Expert Comment

by:Gustav Brock
ID: 39267685
You would only have to use two queries if no IDs existed and you would have to fall back to a field by field comparison.

If you have the IDs, one query will do.

/gustav
0
 
LVL 7

Author Comment

by:ayha1999
ID: 39267690
I have already tried the following code but only one query works. I also tried both insert and update in the loop but didn't work.

insertQuery(insert into table...
Loop 1:
 for (int j = 0; j < gvFiles.Rows.Count; j++)
        {
          insertQuery

...
}

UpdateQuery(Update table...
Loop 2:
 for (int j = 0; j < gvFiles.Rows.Count; j++)
        {
          UpdateQuery

...
}
0
 
LVL 31

Accepted Solution

by:
hnasr earned 400 total points
ID: 39267701
Any feedback on trying the delete then the insert queries?

The idea, if in table1 you have 1, x and 2, y records.
And in table2 you have 1,x record
then delete deletes 1,x from table 2
and insert inserts the new (1,x) as if updated and the other 2,y record.
0
 
LVL 7

Author Comment

by:ayha1999
ID: 39267704
I have new records in gridview and only one table exists. where I have to create table2?
0
 
LVL 53

Assisted Solution

by:Gustav Brock
Gustav Brock earned 200 total points
ID: 39267705
Calling a Query for each row inside a loop is extremely slow.

If you don't want to use straight SQL (which I wouldn't) then use a DataTableAdapter to open the table and you have an object you can search, edit, and append to very fast.

/gustav
0
 
LVL 47

Assisted Solution

by:aikimark
aikimark earned 200 total points
ID: 39267826
create a unique index on your destination table and it will prevent duplicates.  You could then run an append query and only the new/differentID rows would be inserted.  If you are iterating through the source recordset, inserting each record into the destination table, you would trap for a duplicate key exception and keep iterating.
0
 
LVL 50

Assisted Solution

by:Dale Fye
Dale Fye earned 200 total points
ID: 39267852
You can do it with an UPDATE query if both sets of data are in a table or a query result set, but not sure how (other than VBA) to do it with a single query and a data grid.  Assume you have:

Table1
Id   Name
1     abc
2     xyz

and Table2
Id  Name
2   xyz

Then you could write:

UPDATE Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
SET Table2.Name = Table1.Name

IN Access, this works, but I have not found any other relational database that will do this in a single UPDATE query.  This will update records that already exist in Table2 and insert any new records that are in Table1 but not in Table2.  You could replace Table1 with a query if you need to.
0
 
LVL 7

Author Closing Comment

by:ayha1999
ID: 39300593
Thanks
0
 
LVL 31

Expert Comment

by:hnasr
ID: 39301553
Welcome!
0

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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.

Join & Write a Comment

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

585 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