Page 1 of 1

Update rows to match other rows of same table

Posted: Fri Aug 08, 2008 2:25 pm
by enatefox
Hi,
I thought this question was a little noobish and would have it solved quickly but...

<problem>
I have a table that has a set of rows for a given category and I need to copy a given category's rows to another category within the same table. Nothing is working as expected:
</problem>

<example>
[john] [yes] [food]
[john] [yes] [pizza]
[john] [yes] [wacky inflatable arm flailing tubemen]
...
[mary] [no] [food]
[mary] [no] [pizza]
[mary] [no] [wacky inflatable arm flailing tubemen]

So in this case, Mary wants to copy John's responses.
</example>

<attempts>
I tried a few methods but it was fail time for me. This and variations of:

1.)
update tablename
set
a.item_text = b.item_text,
a.mode = b.mode,
from tablename a, tablename b
where a.template_stem = 'mary' and b.template_stem = 'john'
and a.item_title = b.item_title

2.)
update tablename
set
item_text = (select item_text from tablename where template_stem ='john'),
mode = (select mode from tablename where template_stem ='john'),
where template_stem = 'mary'
</attempts>


What can I do? They are joined by the item_title column and I do not want to loop through each row which would work and also make my webpage cry while it waits for the response.

Re: Update rows to match other rows of same table

Posted: Tue Aug 12, 2008 10:59 am
by ghurtado
Which are the columns you are trying to copy? Can we see your schema?

Re: Update rows to match other rows of same table

Posted: Tue Aug 12, 2008 11:23 am
by enatefox
I just went for the easy way and dropped the existing rows and then inserted them so I had access to INSERT's ability to match the desired rows. Maybe not the best practice but it works.

I still haven't figured this out but the issue is dead to me now. Thanks for your help.