an SQL UPDATE command to optimize ?

[foxup]

Hi everybody,

I need a simple optimization for these 4 lines of code. I would like to have it into 1 command instead (if possible). These are the 4 lines of code:

USE C:\union\BOUNCE_SUP990\SOURCE
sele id, VAL(callpathnm) as cps from C:\union\bounce_sup990\source WHERE val(callpathnm)>0 into table r:\digibuzlines
SET RELATION TO Source.id INTO Digibuzlines ADDITIVE
REPLACE source._qty with digibuzlines.cps FOR source.db_desc='DIGITAL BUSINESS LINE' AND source.id = digibuzlines.id

I'm trying to turn it into only 1 UPDATE command, so I've tried the: UPDATE C:\union\bounce_sup990\source SET.... command but the result isn't updating correctly.

Can someone please help me with this quick optimize ?


Thanks,
FOXUP

 

[Chris Miller]

UPDATE table SET field=value WHERE condition. That is the general update syntax.

In your xBase variation you're overcomplicating this as a self join on id, which should join the record with a specific id to itself only, so it really just boils down to a condition independent on the id, because you only introduce the need to make a self referential 1:1 join by splitting data into a second workarea. A self-join will only be used, when you want to update data in the same table but read from several records and write to et another. It's not necessary if you change something in the same record that you check for a condition.

Still you'd have to show some sample data, I guess you actually have lots of records with the same id and that's something that's inherently wrong with the data normalization.

Just following the path of data read and split and joined back together, you take VAL(callpathnm), call that cps and store it away paired with the id. Limited to all records where that's a positive value. There's one restriction. Okay.

The you relate the source data by id into the newly created table. There's your first error, as you don't have an index on your new table you make a relation by record number, and while for most records id=recno()l, that's not the case for digibuzlines, where you only have a part of the whole data. This will be the core reason you finally update the wrong rows. And it's so simple to avoid, as you don't even need to split off that data in the first place. If you do a REPLACE, do you first copy dats to a new dbf and then relate it back to a source reocrds and then replace it through that link? Or do you simply do a REPLACE? A REPLACE FOR, to not just act on a single record.

An Update is exactly that in case you're having the target field and source data for meeting the update condition all in the same table, there's no need to generate a self join here.

UPDATE source.dbf SET _qty TO VAL(callpathnm) WHERE db_desc='DIGITAL BUSINESS LINE' and VAL(callpathnm)>0

That is unless what I fear is true and id really isn't an id, an identifier, a unique identifier, but actually a foreign key, which like userid or customerid or orderid should always have a more qualifying name telling from which other table it is the identifier it is 1:n related to. And in that case you have to become more specific about your data. Maybe even to the point where you have to realize there only is a valid UPDATE or even REPLACE possible, if you restructure your data in a normal form.

Chriss

 

[foxup]

Hello,

That command doesn't get the proper result. If you look carefully at the 4 lines of code, you'll see that I'm replacing source._qty with digibuzlines.cps FOR source.db_desc='DIGITAL BUSINESS LINE' AND source.id = digibuzlines.id

That UPDATE command does not work properly as I need to find the value in source.callpathnm and update source._qty but all the fields are in the same database. I'm updating 1 database from the values and criteria within the same database.

Please help.


Thanks,
FOXUP

 

[Chris Miller]

Can you please show what's not working by giving an example with data?

>I need to find the value in source.callpathnm and update source._qty but all the fields are in the same database. I'm updating 1 database from the values and criteria within the same database.

That's exactly what my UPDATE is doing, it's not doing a self join, as there isn't the need for a self join, the value to SET in one field is coming from the same record. You could add AND id=id to the where clause, but that's always true.



Chriss

 

[foxup]

Hi,

Here is the data:

The _QTY field for "DIGITAL BUSINESS LINES" only needs to be the VAL(callpathnm) respectively as per their ID.

I hope that somebody can help me.


Thanks,
FOXUP!

 

[foxup]

Hi,

The callpathnm is not in the same record as the "DIGITAL BUSINESS LINE", or else I would do a simple replace and it would solve my problem.

The callpathnm is in the same ID but not the same record.

Please help.

Thanks,
FOXUP

 

[Chris Miller]

Just as I imagined, your ID isn't an ID, an identifier, because by it's definition it uniquely identifies a record.

So you have multiple records with the same ID, and you want to set a field to a value that's only stored in one usually other record than itself.

Then you have to change your where condition, I guess. Don't you?
How do you even get to the number in callpathnm? Some preprocessing that does a count?

I don't see that helping you with your demand would actually help you, the step to change is much earlier, i.e. what you want can be much easier done in a step before having all these multiple seemingly all same records, as far as we can see.

To solve your actual problem I'd need to see and learn much more about your data structure. It's just the typical problem to me, that results from disregarding data normalization as some academic idea unnecessary to comply with.

To give a hint where this leads to: You actually now do need a self join that connects all these "other" records to the one having the value you want to spread to multiple records. And well, if I imagine in which way many of the multiple records have the same description coming from a SELECT with a JOIN, that's already the time to also generate multiple copies of the qty. So not defering this to aftermath, you don't just make the aftermath simpler, you even completely remove it.

Chriss

 

[foxup]

I can simply run a GROUP BY afterwards so don't worry about that.


My goal is; I would like to:

REPLACE source._QTY with val(calpathnm) for db_desc='DIGITAL BUSINESS LINE' and VAL(callpathnm)>0 ....and the ID is the same ID.
Note, the val(calpathnm) can only be found when db_desc='CUSTOMER - CUSTOMER' (if that helps)

How can it be done with 1 (or 2 commands) instead of the 4 commands originally posted?

Any help please.


Thanks,
FOXUP

 

[foxup]

Yes, many records have the same ID but there is only 1 where its db_desc='CUSTOMER - CUSTOMER' (for each ID). It is unique and that's where the val(calpathnm) can also be found.

Is there any other way to optimize into an UPDATE or REPLACE with less than 4 commands?

I hear what I you're saying, many ways to skin a cat, i agree, but I'd just like to see this into 1 (or 2) commands if possible.

Any help please.


Thanks,
FOXUP

 

[TamarGranor]

Don't know if you're still looking for an answer here, but let me take a stab at it:

[pre]UPDATE Source ;
SET CallPathNm = ( ;
SELECT SUM(Qty) FROM Source SrcB WHERE db_desc='DIGITAL BUSINESS LINE' AND Source.ID = SrcB.ID) ;
WHERE Db_Desc = 'CUSTOMER-CUSTOMER'[/pre]

If you actually want to set all records in the group with this value, drop the final WHERE.

Tamar