Gossamer Forum
Home : General : Databases and SQL :

Update records in a table based on a function

Quote Reply
Update records in a table based on a function
I am not sure if this can be done purely with SQL, or it will require a perl loop. I am guessing straight SQL is the better performace option.

I have the function (which you helped me write two posts agoWink):

#determine if the category has children
sub has_children { $DB->table('Category')->count( { FatherID => $_[0] } ) }

and I want to go through every record in Category, run the "ID" through my function: has_children($category_id), and if it returns true, SET Category.haschildren = "Yes" ELSE set "No".

UPDATE 'Category' SET 'haschildren' = 'Yes' WHERE has_children('ID') <- my bad attempt


http://www.iuni.com/...tware/web/index.html
Links Plugins
Quote Reply
Re: [sooke] Update records in a table based on a function In reply to
Try:
Code:
my $has_children = (has_children($category_id)) ? 'Yes' : 'No';
$DB->table('Category')->update({ haschildren => $has_children}, { ID => $category_id }

BTW: if you are going to do many selects, updates etc, with the same category, you can save yourself some writing if you define the table object $cat = $DB->table('Category'). Then you can just write $cat in place of $DB->table('Category')

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [yogi] Update records in a table based on a function In reply to
>>
you can save yourself some writing if you define the table object
<<

...as well as overhead.
Quote Reply
Re: [yogi] Update records in a table based on a function In reply to
Thanks Ivan, (and Paul).

You are both responsible for my now dangerous abilities with perl, albiet K2 levelWink

The above code makes sense. Perfect!


http://www.iuni.com/...tware/web/index.html
Links Plugins
Quote Reply
Re: [yogi] Update records in a table based on a function In reply to
Hi Ivan,


Here is what I have:

Code:

$my category_id;
print "Updating...";
my $has_children = (has_children($category_id)) ? 'Yes' : 'No';
$DB->table('Category')->update({ haschildren => $has_children}, { ID => $category_id });
print "done.<br>";

#determine if the category has children
sub has_children { $DB->table('Category')->count( { FatherID => $_[0] } ) }


I added a ); to the end of the $DB-> statement. Must have got chopped off.


http://www.iuni.com/...tware/web/index.html
Links Plugins

Last edited by:

sooke: May 16, 2002, 10:01 AM
Quote Reply
Re: [yogi] Update records in a table based on a function In reply to
Ok, dumb question.

I was assuming that the UPDATE statement would loop through all of the rows.Shocked

Do I have to create a loop around this and and set $category_id equal to each corresponding ID as well?

Example:

For count = 1 to total categories

SELECT $category_id = ID

UPDATE statement from above

Next

The cgi did not change any of the rows in the category table when I ran it... all still blank.


http://www.iuni.com/...tware/web/index.html
Links Plugins
Quote Reply
Re: [sooke] Update records in a table based on a function In reply to
That code should be spewing an error due to

$my category_id;

...which should be:

my $category_id;

Last edited by:

Paul: May 16, 2002, 10:45 AM
Quote Reply
Re: [Paul] Update records in a table based on a function In reply to
Paul, you are so right.Blush

Edit: interestingly enough, it seems I correct that one already! More coffee for me I think.

Thanks for pointing it out though!


http://www.iuni.com/...tware/web/index.html
Links Plugins

Last edited by:

sooke: May 16, 2002, 10:59 AM
Quote Reply
Re: [yogi] Update records in a table based on a function In reply to
I had no luck with this at allFrown.

I have even tried loading all 'ID's into @ID and all the 'FatherID's into @FID, finding which IDs are not present in @FID hoping this would return the set of category ID's which did not have children.Unsure No such luck.

PLEASE, does anyone know how I can use SQL (and/or Perl) to create a list of Category ID's which don't have children? Ideally an update to my 'haschildren' column at the same time would be the best solution.

Thanks.


http://www.iuni.com/...tware/web/index.html
Links Plugins
Quote Reply
Re: [sooke] Update records in a table based on a function In reply to
Try the following:
Code:
my $cat = $DB->table('Categories');
my $sth = $cat->select;

while (my $category = $sth->fetchrow_hashref) {
my $hc = ($cat->count( { FatherID => $category->{ID} } )) ? 'Yes' : 'No';
$cat->update( { haschildren => $hc }, { ID => $category->{ID} } );
}

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [sooke] Update records in a table based on a function In reply to
The following algorithm logic would do this:
- you have to go through each category ID, and check if there is at least one, with this as 'ParentID'.
If there is no one with current ID as ParentID, then you found a category which has no children.

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Quote Reply
Re: [yogi] Update records in a table based on a function In reply to
Yogi I think that may have done it!!!!!!!!!!!!!!!!!!!!!!!!!

I just have to do a thorough check, before I start deleting categories based on this.

Thank you Yogi and Webmaster for all your help on this. This will save me a lot of work!!!!!!

CoolCoolCool


http://www.iuni.com/...tware/web/index.html
Links Plugins
Quote Reply
Re: [sooke] Update records in a table based on a function In reply to
It seems posts of Yogi & mine crossed.
Sooke, I'm glad you got the exact solution. Cool

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Quote Reply
Re: [webmaster33] Update records in a table based on a function In reply to
Yes they might have.... It also seems I may have posted different version of the same problem in two areas. Something I will try to avoide in the future. It was unintentional, but sometimes two seperate problems evolve into the same problem if you know what I mean!


http://www.iuni.com/...tware/web/index.html
Links Plugins