if exist update else insert

[Harold]

HELP ME!!! HELP ME!!!

I want to update a row in a table, but if the row doesn't
exist I want to update it. I tried a lot of queries but I
get the message : "Missing semicon ( at the end of SQL
statement." I am running a macro which runs a sql-
statement. How can I solve this, because there is an
semicon at the end.

Thanks

 

[Michel Walsh]

Hi,


You probably meant, to APPEND it if it does not exist (and UPDATE it if it
exists).

In JET:


Inventory (the table to append to/ update)

NewStock ( table supply new data)



UPDATE Inventory RIGHT JOIN NewStock
ON Inventory.ItemID = NewStock.ItemID
SET Inventory.ItemID= NewStock.ItemID,
Inventory.UnitPrice = NewStock.UnitPrice,
Inventory.Description = NewStock.Description ;



would do.


Hoping it may help,
Vanderghast, Access MVP

 

[Brian Camire]

Wow! I didn't know you could do an INSERT with an UPDATE. Thanks for
sharing.

 

[Michel Walsh]

Hi,


You can update the unpreserved side of an outer join, in JET, if you
read an update like: what I actually SET is what I want see (with a
SELECT) after I complete the update. Indeed, after the update, change the
SET for a SELECT (change the = for a coma), change the UPDATE for a FROM,
move it after the SELECT, and presto, what you have set is now what you
should got (confirmed by pair) (and you can change the RIGHT JOIN for an
INNER JOIN too, for that SELECT, after the update is done).


MS SQL Server does not have that "notion" about what an UPDATE is, and
refuse to let you update the unpreserved side of an outer join (the table
that appear to the left of a RIGHT JOIN, as example).


Hoping it may help,
Vanderghast, Access MVP

 

[Harold]

Thanks, but this is not really what I mean. I have got one
table with an inventory. Not all the items are in stock so
I can not update all the items. If an item exist I can
update it, but when the item doesn't exist no rows will be
updated so I want to insert a new row with that item.

The items are in a other table called products. Items
which are having less than one item will be deleted.

Please help me again.

Best regards,

Harold

 

[Michel Walsh]

Hi,

Thanks, but this is not really what I mean. I have got one
table with an inventory. Not all the items are in stock so
I can not update all the items. If an item exist I can
update it, but when the item doesn't exist no rows will be
updated so I want to insert a new row with that item.

That is exactly what the query does. If the item does not exist in
Inventory (my example) but it is a new one to be append to it (because it
appear, and for the first time, in NewStock), then that item will be
appended in Inventory. Items that do not appear in NewStock won't be
modified. Items that are in NewStock and in Inventory will be updated, in
Inventory.

The items are in a other table called products. Items
which are having less than one item will be deleted.

You will have to do the delete in another query, like


DELETE FROM Inventory WHERE qty < 1



after you would have do the update/append.




Hoping it may help,
Vanderghast, Access MVP