if else with insert update

A

Andrew

Hi all,

I'm new with access queries, I've mostly delt with stored procedures.

Is is possible to write a query that has an
if/else statement e.g.
if KeyID exist, then update ...
else if KeyID not exist, then insert. ....


also is it possible to call a query from within another query like a nested
sp ?
 
K

KARL DEWEY

1- Use IIF function. IIF(Test for True, Results for True, Results for
False) These may be nested.

2- Known as subquery. If you do not know subqueries then use first query in
second joined with orignal table.
 
V

vanderghast

You can do an insert/update in Jet by updating the unpreserved side of an
outer join. Example: tables Old and Mod:, Mod modifying unit price of
itemID:



UPDATE old RIGHT JOIN mod ON old.ItemID = mod.ItemID
SET old.itemID=mod.ItemID,
old.unitPrice = mod.unitPrice



which will update the unit price for existing itemID, and append new record,
in Old, for new itemId (that is why you need to set old.itemID=mod.ItemID
EVEN if the ON clause tell the same: since we have an outer join, old.itemID
place holder MAY be null, before the update, and, if the occurs, we want
this null to be replaced by mod.itemID).



Vanderghast, Access MVP
 
A

Andrew

sorry but I don't really understand you explainations.

1) From what I understand from your reply, can I do something this ?
e.g.
PARAMETERS KeyID Long, PatID IEEEDouble;
SELECT [Key ID], [Patient ID], IIF( [Key ID] <> null ,
"insert into KeyTable ( [Key ID], [Patient ID]) values (KeyID, PatID)",
"update KeyTable set [P ID] = PatID where [Key ID] = KeyID") AS Expr1
FROM KeyTable
WHERE [Key ID] = KeyID;

2) Do you know of any resource or webpage that I can lookup more about
querries?
 
K

KARL DEWEY

I think you want to do two different things in a single query.

Update if record exist OR Append if record not exist.

If this is what you are wanting to do then I do not think it is possible in
a single query.


Andrew said:
sorry but I don't really understand you explainations.

1) From what I understand from your reply, can I do something this ?
e.g.
PARAMETERS KeyID Long, PatID IEEEDouble;
SELECT [Key ID], [Patient ID], IIF( [Key ID] <> null ,
"insert into KeyTable ( [Key ID], [Patient ID]) values (KeyID, PatID)",
"update KeyTable set [P ID] = PatID where [Key ID] = KeyID") AS Expr1
FROM KeyTable
WHERE [Key ID] = KeyID;

2) Do you know of any resource or webpage that I can lookup more about
querries?

--
Thanks in advance

regards,
Andrew


KARL DEWEY said:
1- Use IIF function. IIF(Test for True, Results for True, Results for
False) These may be nested.

2- Known as subquery. If you do not know subqueries then use first query in
second joined with orignal table.
Click to expand...
Click to expand...
 
V

vanderghast

Have you tried the update query I proposed? Maybe the message didn't made
it (it seems some messages have difficulties to appear), so I repost it
here:



You can do an insert/update in Jet by updating the unpreserved side of an
outer join. Example: tables Old and Mod:, Mod modifying unit price of
itemID:



UPDATE old RIGHT JOIN mod ON old.ItemID = mod.ItemID
SET old.itemID=mod.ItemID,
old.unitPrice = mod.unitPrice



which will update the unit price for existing itemID, and append new record,
in Old, for new itemId (that is why you need to set old.itemID=mod.ItemID
EVEN if the ON clause tell the same: since we have an outer join, old.itemID
place holder MAY be null, before the update, and, if the occurs, we want
this null to be replaced by mod.itemID).



Vanderghast, Access MVP




Andrew said:
sorry but I don't really understand you explainations.

1) From what I understand from your reply, can I do something this ?
e.g.
PARAMETERS KeyID Long, PatID IEEEDouble;
SELECT [Key ID], [Patient ID], IIF( [Key ID] <> null ,
"insert into KeyTable ( [Key ID], [Patient ID]) values (KeyID, PatID)",
"update KeyTable set [P ID] = PatID where [Key ID] = KeyID") AS Expr1
FROM KeyTable
WHERE [Key ID] = KeyID;

2) Do you know of any resource or webpage that I can lookup more about
querries?

--
Thanks in advance

regards,
Andrew


KARL DEWEY said:
1- Use IIF function. IIF(Test for True, Results for True, Results for
False) These may be nested.

2- Known as subquery. If you do not know subqueries then use first query
in
second joined with orignal table.
Click to expand...
Click to expand...
 
K

KARL DEWEY

I get a Syntax error (missing operator) in query expression 'old.ItemID =
mod.ItemI'.

vanderghast said:
Have you tried the update query I proposed? Maybe the message didn't made
it (it seems some messages have difficulties to appear), so I repost it
here:



You can do an insert/update in Jet by updating the unpreserved side of an
outer join. Example: tables Old and Mod:, Mod modifying unit price of
itemID:



UPDATE old RIGHT JOIN mod ON old.ItemID = mod.ItemID
SET old.itemID=mod.ItemID,
old.unitPrice = mod.unitPrice



which will update the unit price for existing itemID, and append new record,
in Old, for new itemId (that is why you need to set old.itemID=mod.ItemID
EVEN if the ON clause tell the same: since we have an outer join, old.itemID
place holder MAY be null, before the update, and, if the occurs, we want
this null to be replaced by mod.itemID).



Vanderghast, Access MVP




Andrew said:
sorry but I don't really understand you explainations.

1) From what I understand from your reply, can I do something this ?
e.g.
PARAMETERS KeyID Long, PatID IEEEDouble;
SELECT [Key ID], [Patient ID], IIF( [Key ID] <> null ,
"insert into KeyTable ( [Key ID], [Patient ID]) values (KeyID, PatID)",
"update KeyTable set [P ID] = PatID where [Key ID] = KeyID") AS Expr1
FROM KeyTable
WHERE [Key ID] = KeyID;

2) Do you know of any resource or webpage that I can lookup more about
querries?

--
Thanks in advance

regards,
Andrew


KARL DEWEY said:
1- Use IIF function. IIF(Test for True, Results for True, Results for
False) These may be nested.

2- Known as subquery. If you do not know subqueries then use first query
in
second joined with orignal table.

:

Hi all,

I'm new with access queries, I've mostly delt with stored procedures.

Is is possible to write a query that has an
if/else statement e.g.
if KeyID exist, then update ...
else if KeyID not exist, then insert. ....


also is it possible to call a query from within another query like a
nested
sp ?
--
Thanks in advance

regards,
Andrew
Click to expand...
Click to expand...
Click to expand...
 
V

vanderghast

Strange. (Maybe mod is confusing with the operator of the same name). Have
you tried graphically? Bring the two tables, join them on ItemID, edit the
join to keep all records from Mod. Change the query type to an Update.
Update the fields FROM TABLE old, to their equivalent from table old (need
[ ] around the table name). I got, with square brackets, the following
statement in SQL view :

UPDATE Old RIGHT JOIN Mod ON Old.ItemID = [Mod].ItemID SET Old.ItemID =
[mod].[itemid], Old.UnitPrice = [mod].[unitPrice];


And starting with


Old
---------------------
ItemID UnitPrice
1 1
2 2

Mod
-----------------------
ItemID UnitPrice
1 1.1
3 3



If got, after the query:


Old:
-------------------
ItemID UnitPrice
1 1.1
2 2
3 3



Vanderghast, Access MVP




KARL DEWEY said:
I get a Syntax error (missing operator) in query expression 'old.ItemID =
mod.ItemI'.

vanderghast said:
Have you tried the update query I proposed? Maybe the message didn't
made
it (it seems some messages have difficulties to appear), so I repost it
here:



You can do an insert/update in Jet by updating the unpreserved side of an
outer join. Example: tables Old and Mod:, Mod modifying unit price of
itemID:



UPDATE old RIGHT JOIN mod ON old.ItemID = mod.ItemID
SET old.itemID=mod.ItemID,
old.unitPrice = mod.unitPrice



which will update the unit price for existing itemID, and append new
record,
in Old, for new itemId (that is why you need to set old.itemID=mod.ItemID
EVEN if the ON clause tell the same: since we have an outer join,
old.itemID
place holder MAY be null, before the update, and, if the occurs, we want
this null to be replaced by mod.itemID).



Vanderghast, Access MVP




Andrew said:
sorry but I don't really understand you explainations.

1) From what I understand from your reply, can I do something this ?
e.g.
PARAMETERS KeyID Long, PatID IEEEDouble;
SELECT [Key ID], [Patient ID], IIF( [Key ID] <> null ,
"insert into KeyTable ( [Key ID], [Patient ID]) values (KeyID,
PatID)",
"update KeyTable set [P ID] = PatID where [Key ID] = KeyID") AS Expr1
FROM KeyTable
WHERE [Key ID] = KeyID;

2) Do you know of any resource or webpage that I can lookup more about
querries?

--
Thanks in advance

regards,
Andrew


:

1- Use IIF function. IIF(Test for True, Results for True, Results
for
False) These may be nested.

2- Known as subquery. If you do not know subqueries then use first
query
in
second joined with orignal table.

:

Hi all,

I'm new with access queries, I've mostly delt with stored
procedures.

Is is possible to write a query that has an
if/else statement e.g.
if KeyID exist, then update ...
else if KeyID not exist, then insert. ....


also is it possible to call a query from within another query like a
nested
sp ?
--
Thanks in advance

regards,
Andrew
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I was just trying to save for later.

It saved this time with the brackets.

vanderghast said:
Strange. (Maybe mod is confusing with the operator of the same name). Have
you tried graphically? Bring the two tables, join them on ItemID, edit the
join to keep all records from Mod. Change the query type to an Update.
Update the fields FROM TABLE old, to their equivalent from table old (need
[ ] around the table name). I got, with square brackets, the following
statement in SQL view :

UPDATE Old RIGHT JOIN Mod ON Old.ItemID = [Mod].ItemID SET Old.ItemID =
[mod].[itemid], Old.UnitPrice = [mod].[unitPrice];


And starting with


Old
---------------------
ItemID UnitPrice
1 1
2 2

Mod
-----------------------
ItemID UnitPrice
1 1.1
3 3



If got, after the query:


Old:
-------------------
ItemID UnitPrice
1 1.1
2 2
3 3



Vanderghast, Access MVP




KARL DEWEY said:
I get a Syntax error (missing operator) in query expression 'old.ItemID =
mod.ItemI'.

vanderghast said:
Have you tried the update query I proposed? Maybe the message didn't
made
it (it seems some messages have difficulties to appear), so I repost it
here:



You can do an insert/update in Jet by updating the unpreserved side of an
outer join. Example: tables Old and Mod:, Mod modifying unit price of
itemID:



UPDATE old RIGHT JOIN mod ON old.ItemID = mod.ItemID
SET old.itemID=mod.ItemID,
old.unitPrice = mod.unitPrice



which will update the unit price for existing itemID, and append new
record,
in Old, for new itemId (that is why you need to set old.itemID=mod.ItemID
EVEN if the ON clause tell the same: since we have an outer join,
old.itemID
place holder MAY be null, before the update, and, if the occurs, we want
this null to be replaced by mod.itemID).



Vanderghast, Access MVP




sorry but I don't really understand you explainations.

1) From what I understand from your reply, can I do something this ?
e.g.
PARAMETERS KeyID Long, PatID IEEEDouble;
SELECT [Key ID], [Patient ID], IIF( [Key ID] <> null ,
"insert into KeyTable ( [Key ID], [Patient ID]) values (KeyID,
PatID)",
"update KeyTable set [P ID] = PatID where [Key ID] = KeyID") AS Expr1
FROM KeyTable
WHERE [Key ID] = KeyID;

2) Do you know of any resource or webpage that I can lookup more about
querries?

--
Thanks in advance

regards,
Andrew


:

1- Use IIF function. IIF(Test for True, Results for True, Results
for
False) These may be nested.

2- Known as subquery. If you do not know subqueries then use first
query
in
second joined with orignal table.

:

Hi all,

I'm new with access queries, I've mostly delt with stored
procedures.

Is is possible to write a query that has an
if/else statement e.g.
if KeyID exist, then update ...
else if KeyID not exist, then insert. ....


also is it possible to call a query from within another query like a
nested
sp ?
--
Thanks in advance

regards,
Andrew
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Page numbering with Merged Document and IF statements 1
Cross-reference Insert Default Change 0
Calling Parameterized Query Using SQL Syntax 3
INSERT INTO from query 1
How do I execute multiple SQL statements in Access?? 3
Help with update subquery 3
update query 2
Updateable Query Problem 9

Top