Update rows to match other rows of same table
Posted: Fri Aug 08, 2008 2:25 pm
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.
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.