Solved

SQL statement

Posted on 2013-06-21
18
353 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 49

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
 
LVL 12

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 49

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 30

Assisted Solution

by:hnasr
hnasr earned 100 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 49

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 30

Accepted Solution

by:
hnasr earned 100 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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 50 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 45

Assisted Solution

by:aikimark
aikimark earned 50 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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 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 30

Expert Comment

by:hnasr
ID: 39301553
Welcome!
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now