How do I write back by line item?

Hello,

I'm writting a program in VB to connect to an AS/400 via ODBC.

The customer I am working with has a table of sales orders.

A sales order may contain 50-100 line items. Each line would be for a different item associated with the same order.

As of right now, I would be using the item name to identify where to write back an update to the line item.

What I am wondering is, can I write back by line item instead?

They don't seem to have a key index, but I am going with what they are telling me.

So would this be possible? I have numerous books on different versions of SQL but I thought I would post the question just incase I can get a response here before I page through my books.

Thanks,

You would need to know what the unique key(s) is(are) in order to do updates per record. I presume your AS/400 database will accept ANSI SQL statements, so just do an update where your 'where' clause contains enough elements to ensure uniqueness.

Thanks, but that is how I am doing it now.

I need to write back by line item, not a key index. I thought there was a way you could specify which line item to write too.

For instance, suppose I am writting back to a MDB through ODBC.

ORDER, ITEM, QTY, OPTION
12345, WIDGET, 5, null
12345, WIDGET, 3, RED
12345, WIDGET, 3, BLUE
12345, WIDGET, 5, RED
12345, WIDGET, 5, BLUE
12345, GADGET, 3, RED
etc...

Based on the above, I would have to write a select like this...

SELECT * FROM SALES WHERE ORDER = '12345' AND ITEM = 'WIDGET' AND QTY = '3'AND OPTION = 'RED'

That can get cumbersome. Wouldn't it be easier if I could index and sort then writeback to a specific line using the same sort?

See, I also have to work with a database that stores customer orders, but adds lines using the same order for each option they add to their order.

I may have to just do a lot of coding, but I thought I saw somebody do it by line item before.

If I am understanding what you are doing, you want to do an update without all the where clauses. That only works if your database has unique keys in the table. Some databases do that automagically, others don't. If your DBAs say there is no primary key, I think you are probably SOL.

You should be writing this in code, so once written all should be done in a loop, so whats the big deal?

The big deal was finding out if I could write by line item instead of coding everything and get the same result. You don't know if you don't ask or come across it reading. So I asked.

Just like if somebody is writting in PHP.

print $strReturn = ( $value = 12) ? "Yes it is!":"No it's not";

is an easy way of writting all this...

If $value = 12 then {
$strReturn = "Yes it is!"
}
else {
$strReturn = "Not it's not"
}
print $strReturn;

If somebody didn't ask they wouldn't know and that's much easier.

So there is no big deal when it comes to a question because no question is stupid. In fact, writting back by a line item is a great idea that should be incorporated as a standard function in a future release of a SQL extention.