SQL Update with a delete / update / insert in one go
in [Visual Objects]
|
Prev: Register problem Report Pro
Next: Error in OLE Client.
From: Johan Nel on 26 Oct 2007 03:15 Geoff Schaller wrote: Hi Geoff, > I think your primary PK design is wrong at the item level. The way you > described it doesn't really make sense. You either need a unique row Well you see although you feel it is maybe a fruitless exercise, I do read what you and others are saying... <g> Firstly, I can change my database design in SQL to have TagNo as the PK, as it is unique. Secondly I can have an index on FormNo for the FK which is non-unique to group TagNo's that should be displayed on one form saving in DB space by not having the TagNo in that index. > number or ID as the PK or no PK at all. Having no PK is no sin and often > is warranted. In fact there are some people who believe clustered Yes I agree 100% and I use that in the LogErr table with only a FK on FormNo. > You certainly shouldn't have a PK on order number plus type and then use > the qty field to indicate multiple types. This just doesn't make sense. > In sales terms, for instance, it means you could never have two items of > the same type with different prices. Agree, was just using my approach as an example to try and get where I am coming from. Maybe Order and Order detail was not the right example to use though. > But you haven't said what is inefficient? Well the delete all TagNo's and then "re-inserting" them is probably not optimal. And could lead to database corruption if the server/network/power goes down while the process is running for a start. > As I said several posts ago (and confirmed by another correspondent) > that I would prefer a different process where you didn't delete > everything and then re-insert. Your process prohibits anyone being able > to access this "order" until you complete your process and to me, that > will be a serious contention issue in a busy environment. However I have > acknowledged it can work. Yes I acknowledge this and I will surely look deeper into this to "re-design" the transaction. > So you are looking for ideas. About what? Well you (and others) gave me ideas to ponder on in all the posts. And I appreciate it. The TagNo as unique discussion however did side-track the issue a bit though and I do apologise, but yes I realised in the end that I made a mistake by setting the PK as FormNo + TagNo, instead of only TagNo and have FormNo as FK. I will get back to how I implement this one. Thanks for all the discussion, I do appreciate it. Regards, -- Johan Nel Pretoria, South Africa.
From: Geoff Schaller on 26 Oct 2007 06:23
Johan, I feel like this is going around in circles <g>. > Firstly, I can change my database design in SQL to have TagNo as the PK, > as it is unique. Secondly I can have an index on FormNo for the FK which > is non-unique to group TagNo's that should be displayed on one form > saving in DB space by not having the TagNo in that index. Ok.. whatever. It is up to you but don't get worried about saving disk space. It is trivial in this day and age. > > But you haven't said what is inefficient? > Well the delete all TagNo's and then "re-inserting" them is probably not > optimal. Well wasn't that what we were trying to tell you all along? <g> > And could lead to database corruption if the > server/network/power goes down while the process is running for a start. No. Especially no if you are going to use explicit transactions. Don't worry about this. Worry about contention. > I appreciate it. The TagNo as unique discussion however did side-track > the issue a bit though and I do apologise, but yes I realised in the end That is what happens when you get specific <g>. But there is a way out.. Cheers, Geoff |