SQL Query Question

[TyroDBA]

I am looking for a better method to update a table than a utilizing a series of looping complex select/update statements. Some ideas/suggestions on how to structure the code would be great.

The requirement is to take the input table and arrange the order per the specific order table AND substitute where necessary to update
an output table. The code must handle any input table and specific order table combination or variation.

Below is a simplified example.

My existing code worked great until
there were different DAIRY items in the input table, and/or DAIRY was listed twice in the specific order table.

Input Table
-----------
Milk
Beer
Cheese
Hotdogs

Specific Order Table
---------------------
order item
1 Hotdogs
2 Dairy
3 Beer
4 Dairy

Dairy Lookup Table
------------------
Cheese
Milk
Ice Cream
Butter

Output Table
--------------------
order item
1 Hotdogs
2 Milk
3 Beer
4 Cheese

(Don't care about the resulting order of dairy items)

Hope you can help!

[skhanal]

Can you clarify further.

[TyroDBA]

I hope this clarifies things a bit.

I've written most of the SQL code and I'm only having difficulty in one area.

How do I write a query to only return
the first matching record??

My example output table now looks like this

Output Table
------------
Order Item
1 Hotdogs
2 Dairy
3 Beer
4 Dairy

Now I need to set each "Dairy" record
to a dairy item that's in the input record AND the lookup table.

A query of the lookup table and the input table for the first dairy item will return two rows, and cause an update to fail.

Once this part is working, the query of the second dairy item would be easy...I would exclude any dairy items that are already in the output table.

Here is the desired final output.

Output Table
------------
Order Item
1 Hotdogs
2 Milk
3 Beer
4 Cheese

(The order of dairy products themselves doesn't matter)

[skhanal]

How do you know the order item by joining input and lookup table?. You will have to post the complete structure of each table and your update statement, I am still at loss.

[TyroDBA]

skhanal, Thanks for attempting to answer my request. Attached is my solution to the example DB. The form/text explains how it might be implemented in the SQL trigger code for the realworld application (polymer formulations). Setting this up in Access made for easy and fast offline testing. Feel free to submit any comments or responses about the solution. Thanks again, TyroDBA.

[TyroDBA]

I revised the first query and corrected my example.

[TyroDBA]

I was wondering why I couldn't just replace the constants in the update statement with the two select statements...I did, but when running it I recieve an "Operation must use an updatable query" message. By substituting each select with the constants, I detemined the problem is in the SET portion of the update. I'm guessing its returning too many rows. By its self that select statement works fine. Any ideas? I tried defining and output table (tblOutput AS o)in various places and it did'nt help. Here's the update statement:

UPDATE tblOutput SET OutputItem = (SELECT min(i.item)
FROM tblInput AS i, tblDairyLookup AS d
WHERE i.Item= d.Item
AND NOT EXISTS (SELECT OutputItem
FROM tblOutput
WHERE OutputItem=i.Item))
WHERE ItemOrder = (SELECT min(ItemOrder)
FROM tblOutput
WHERE OutputItem= 'dairy'
GROUP BY OutputItem);