Hello I'm trying to create an efficient update. The table i am trying to update has only primarykey fields.

the short

Using Selfservicing, v1.0.2004.1, sqlserver, vb

Is it possible to use updatemulti to update a table that has 2 fields, both are part of the primary key. The actionquery returned by DynamicQueryEngine.CreateUpdateDQ contains a command object with no SQL in it, no calls are being made to the database.

If not is there a way to trick the CreateUpdateDQ to think the field is not part of the PK?

the long

Here are the details of my problem, maybe i am just doing something wrong.

Basically containers have many packages, and packages can be in many containers, so there is a containerPackage table. I am trying to move several packages to another container. The most efficient way to do this is by updating the ContainerPackage Entity using an updatemulti and changing containerid to the id of the new container.

Here's my code

                    Dim fieldsToUpdate As New ContainerPackageEntity
                    'UPDATE tblContainerPackage SET ContainerID = <destinationContainerID>
                    fieldsToUpdate.ContainerId = destinationContainer.ContainerId()

                    'FROM tlbContainerPackages
                    Dim containerPackages As New ContainerPackageCollection

                    Dim joins As New RelationCollection
                    'INNER JOIN tblPackage ON (tblCollectionPackage.PackageID = Collection.CollectionID)
                    joins.Add(PackageEntity.Relations.ContainerPackageEntityUsingPackageId)

                    'INNER JOIN tblShipment ON (tblPackage.ShipmentID = tblShipment.ShipmentID)
                    joins.Add(ShipmentEntity.Relations.PackageEntityUsingShipmentId)


                   'WHERE
                    Dim where As New PredicateExpression
                    '(tblContainerPackage.ContainerID = <currentContainerID>) 
                    where.Add(PredicateFactory.CompareValue(ContainerPackageFieldIndex.ContainerId, ComparisonOperator.Equal, currentContainer.ContainerId))

                    Dim airportsWhere As PredicateExpression

                    If currentContainer.AirportCodeFilter.Count > 0 Then
                        airportsWhere = New PredicateExpression
                        For Each airport As AirportEntity In currentContainer.AirportCodeFilter
                            'OR AirportCode = <code>
                            airportsWhere.AddWithOr(PredicateFactory.CompareValue(AirportFieldIndex.AirportCode, ComparisonOperator.Equal, airport.AirportCode))
                        Next

                        where.AddWithAnd(airportsWhere)

                    End If

                    containerPackages.UpdateMulti(fieldsToUpdate, where, joins)

short term For now i will just delete and re-add the entities with a loop, but this seems like it will be slow.

leme know thanks,

Gabe

Please check the following thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=630

IMHO I always use a surrogate Key in tables like this, for example:

CustomerOrder Id (PK) CustomerID (FK) OrderID (Fk)

Which makes everything eisier to handle.

Walaa,

Yes, thanks I read this, before i posted, but i don't think it will help with the update I am trying to perform.

You are right about adding the seperate PK, it would make things much easier.

For now i will just try to work around this by changing 1 update into lots of small updates.

I will accept the answer that it is poor DB design.

Thanks,

Gabe