Update rows to match other rows of same table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
enatefox
Forum Newbie
Posts: 11
Joined: Fri Apr 04, 2008 12:58 pm

Update rows to match other rows of same table

Post 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.
User avatar
ghurtado
Forum Contributor
Posts: 334
Joined: Wed Jul 23, 2008 12:19 pm

Re: Update rows to match other rows of same table

Post by ghurtado »

Which are the columns you are trying to copy? Can we see your schema?
enatefox
Forum Newbie
Posts: 11
Joined: Fri Apr 04, 2008 12:58 pm

Re: Update rows to match other rows of same table

Post 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.
Post Reply