Update if record found otherwise insert new record

  • Thread starter Thread starter Joey
  • Start date Start date
J

Joey

Hello All,

I would like to Update a record if it satisfies a condition (i.e. the
Primary key exists) otherwise I would like to Insert a new record into
that table. Is there a simple and efficient way of doing this. I'm not
sure if the update statement will do this on its own. I will have to do
this to several tables with thousands of records.

Thanks,
Joey.
 
Joey said:
Hello All,

I would like to Update a record if it satisfies a condition (i.e. the
Primary key exists) otherwise I would like to Insert a new record into
that table. Is there a simple and efficient way of doing this. I'm not
sure if the update statement will do this on its own. I will have to do
this to several tables with thousands of records.

Thanks,
Joey.
Click to expand...

Here's an example with a table called Transaction that is updated from
another table called Update Table. The PK in both is MemberNumber.

UPDATE [Update Table] LEFT JOIN [Transaction] ON [Update Table].MemberNumber
= Transaction.MemberNumber SET [Transaction].MemberID = [Update
Table].[MemberID], [Transaction].MemberNumber = [Update
Table].[MemberNumber], [Transaction].TransAmt = [Update Table].[TransAmt],
[Transaction].[Transaction Type] = [Update Table].[Transaction Type];

This doesn't work in standard SQL but does work in Access.

Tom Lake
 
Tom said:
Hello All,

I would like to Update a record if it satisfies a condition (i.e. the
Primary key exists) otherwise I would like to Insert a new record into
that table. Is there a simple and efficient way of doing this. I'm not
sure if the update statement will do this on its own. I will have to do
this to several tables with thousands of records.

Thanks,
Joey.
Click to expand...


Here's an example with a table called Transaction that is updated from
another table called Update Table. The PK in both is MemberNumber.

UPDATE [Update Table] LEFT JOIN [Transaction] ON [Update Table].MemberNumber
= Transaction.MemberNumber SET [Transaction].MemberID = [Update
Table].[MemberID], [Transaction].MemberNumber = [Update
Table].[MemberNumber], [Transaction].TransAmt = [Update Table].[TransAmt],
[Transaction].[Transaction Type] = [Update Table].[Transaction Type];

This doesn't work in standard SQL but does work in Access.

Tom Lake
Click to expand...

I must execute an SQL statement on an SQL Server via SQL Passthrough
statements and I would like to batch numerous statements together and
roll them into a transaction. Thus that solution doesn't really work
for me. Would you happen to know of a general SQL solution without
having to query for each record?

Thanks,
Joey.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In SQL Server you would use a stored procedure, something like this:

create procedure update_account (
@accountID int,
@amount money
)

if exists (select * from accounts where accountid = @accountid)
-- edit
update accounts set amount = @amount where accountid = @accountID
else
-- add
insert into accounts (accountid, amount) values (@accountID, @amount)


Then call it using the following SQL in an SPT:

exec update_account 22335, 4000.25

If using DAO you can set up the QueryDef this way in VBA:

CurrentDB.QueryDefs("myQry").SQL = "exec update_account 22335, 4000.25"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkhcm4echKqOuFEgEQK1OwCguj+rtsligQ9SvBtiGCxO7lsr7W8An3Pe
t99KaWxsI1HHVQgZItPZ5pr0
=uY1v
-----END PGP SIGNATURE-----

Tom said:
Hello All,

I would like to Update a record if it satisfies a condition (i.e. the
Primary key exists) otherwise I would like to Insert a new record
into that table. Is there a simple and efficient way of doing this.
I'm not sure if the update statement will do this on its own. I will
have to do this to several tables with thousands of records.

Thanks,
Joey.
Click to expand...



Here's an example with a table called Transaction that is updated from
another table called Update Table. The PK in both is MemberNumber.

UPDATE [Update Table] LEFT JOIN [Transaction] ON [Update
Table].MemberNumber = Transaction.MemberNumber SET
[Transaction].MemberID = [Update Table].[MemberID],
[Transaction].MemberNumber = [Update Table].[MemberNumber],
[Transaction].TransAmt = [Update Table].[TransAmt],
[Transaction].[Transaction Type] = [Update Table].[Transaction Type];

This doesn't work in standard SQL but does work in Access.

Tom Lake
Click to expand...

I must execute an SQL statement on an SQL Server via SQL Passthrough
statements and I would like to batch numerous statements together and
roll them into a transaction. Thus that solution doesn't really work
for me. Would you happen to know of a general SQL solution without
having to query for each record?

Thanks,
Joey.
Click to expand...
 
Back
Top