if else with insert update

  • Thread starter Thread starter Andrew
  • Start date Start date
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 ?
 
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.
 
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?
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top