SQL Update Row Implicit Iterations

[stanlyn]

Hi,

I have not been able to get this to work... In English lingo, I'm wanting to update the AssetPK (int) field in the Error table with the value of PK coming from the Asset table also an integer. Both tables contains a character field named "AssetKey" with the same values that can be used to pair up with. The AssetKey from the Asset table is unique with only a single row containing it )parent), while the Error table can have many, (children).

All of this because I'm changing the parent/child relationship keys from character to integer, which should help in performance, or is it worth the trouble?

Update error
set
Error.AssetPk = (select Pk from Asset where Asset.AssetKey = Error.AssetKey)

Running this returns...
Msg 512, Level 16, State 1, Line 28 - Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Thanks for any pointers,
Stanley

 

[fredericofonseca]

error is self explanatory - that is at least 1 case where the AssetKey contains 2 records on table Asset.

see what this query gives you

select AssetKey, Count(*) as cnt 
from Asset
group by AssetKey
having count(*) > 1

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[stanlyn]

Hi fredericofonseca,

Not what I wanted to see or expected. The asset table has 169 duplicates, when there should be no duplicates, so looks like I have some work to do...

Now, back to the original question on how to update all the error.assetpk fields to contain the pk value from the asset table. The updating of the error table is not related to the duplicate errors that your scrip revealed. I'll fix that first, then I'll be back to the original question.

Assuming no Asset duplicates, will my command above do what I described?

Thanks,
Stanley

 

[Andrzejek]

Even after fixing the duplicates in Asset table, you may still have multiple records for the same AssetKey in Error table, right? In this case I would do:

[tt]select [blue]DISTINCT[/blue] Pk from Asset where Asset.AssetKey = Error.AssetKey[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[stanlyn]

Yes Andy, The error table can have many as it is a acting as a child table. The Asset table can only have one.

My initial review on how these duplicates got there has proven that VFP's sys(2015) can produce duplicates if coming from different machines. This is proof as the duplicates are from different machines from different counties. From 7 different counties totaling 2.9 million rows, I have 169 duplicates and now the task is finding, evaluating and fixing all child tables.

Thanks,
Stanley

 

[stanlyn]

Andy,

>> select DISTINCT Pk from Asset where Asset.AssetKey = Error.AssetKey

Returns... The multi-part identifier "Error.AssetKey" could not be bound.

Both tables exist and each table has a field named 'AsstKey'

So, not sure what this means or what to do since all parts do exist.

Could this be the result that Asset still has duplicates?

Thanks,
Stanley

 

[Andrzejek]

The way I understand:
>The Asset table can only have one.
One AssetKey value, right? Is that the Primary Key field in your Asset table?
I guess it should be a PK field, but it is not since you
>can produce duplicates if coming from different machines.

If that's the case, your data base design is wrong :-(


---- Andy

There is a great need for a sarcasm font.

 

[stanlyn]

Andy,

The asset table has two fields that are relevant at this point and at a later date AssetKey column will be dropped. Currently, I have a new column named "pk" and the old column named "AssetKey".

The data comes from 7 different vfp databases, from 7 different locations where the unique column was AssetKey which was populated by vfp's sys(2015) function that is supposed to create unique keys. Turns out it does allow a few duplicates in. Out of 2.9 million records by combing all 7 databases into one there are 169 duplicates. Not bad, but bad...

The new Asset.pk field will be replacing the old vfp AssetKey field when all duplicates have been eliminated and all other child tables have new parent child links switched from AssetKey to AssetPK, linking to the new Asset.pk field. All child tables have their own pk field as well as a field named TableX.AssetPK that maps to the pk field in the asset table.

Hope that explains it better and please, when you say "If that's the case, your data base design is wrong", please answer with a "why it is wrong" answer...

When I ask questions here, I try to provide just enough details for an answer and not spell out every little detail that would make this post so long no one would read it.

Thanks,
Stanley

 

[Andrzejek]

The new Asset.pk field will be replacing the old vfp AssetKey field"
I hope you will make Asset.pk field a Primary Key field in your Asset table. That will assure this field is unique and canNOT be repeated.
That was what I was trying to say when I mentioned 'wrong' design. Just naming the field a PK does not make it a Primary Key field, you need to set it up as a Primary Key field in a table.


---- Andy

There is a great need for a sarcasm font.