Page 1 of 1

Update Multiple Rows

Posted: Fri Jan 30, 2009 9:37 am
by psurrena
I want to update multiple rows in my DB. I cannot run each update individually because it may update what was updated right before. Is there a way to get the query below to work?

[sql] UPDATE people_membership SET membership_id=6, membership_id=4 WHERE membership_id=2 AND membership_id=3; [/sql]

I know I can do it in two steps like to prevent updating the update BUT I was hoping there was a better way:
[sql] UPDATE people_membership SET membership_id=106 WHERE membership_id=2;UPDATE people_membership SET membership_id=104 WHERE membership_id=3;  [/sql]
THEN
[sql] UPDATE people_membership SET membership_id=6 WHERE membership_id=106;UPDATE people_membership SET membership_id=4 WHERE membership_id=104;  [/sql]

Re: Update Multiple Rows

Posted: Fri Jan 30, 2009 5:52 pm
by VladSun
You have to explain in more details and give better examples, because I can't understand why you can't use a query like this:
[sql]UPDATE people_membership SET membership_id=6 WHERE membership_id=2;UPDATE people_membership SET membership_id=4 WHERE membership_id=3; [/sql]

Re: Update Multiple Rows

Posted: Fri Jan 30, 2009 6:10 pm
by Benjamin
This the only way I could figure out how to do it without using a stored procedure and this is a real hack job. Be sure to test it thoroughly to ensure that you get the correct results.

Code: Select all

 
UPDATE
  people_membership pm
SET
  pm.membershipd_id = 
    IF (
      pm.membership_id = 2,
      pm.membership_id = 6,
      pm.membership_id = pm.membership_id
    ),
  pm.membershipd_id = 
    IF (
      pm.membership_id = 3,
      pm.membership_id = 4,
      pm.membership_id = pm.membership_id
)
 

Re: Update Multiple Rows

Posted: Fri Jan 30, 2009 6:14 pm
by VladSun
astions wrote:This the only way I could figure out how to do it without using a stored procedure and this is a real hack job. Be sure to test it thoroughly to ensure that you get the correct results.

Code: Select all

 
UPDATE
  people_membership pm
SET
  pm.membershipd_id = 
    IF (
      pm.membership_id = 2,
      pm.membership_id = 6,
      pm.membership_id = pm.membership_id
    ),
  pm.membershipd_id = 
    IF (
      pm.membership_id = 3,
      pm.membership_id = 4,
      pm.membership_id = pm.membership_id
)
 
Did you try it ;)

Re: Update Multiple Rows

Posted: Fri Jan 30, 2009 6:15 pm
by Benjamin
Yes there are no parse errors.

Re: Update Multiple Rows

Posted: Fri Jan 30, 2009 6:24 pm
by VladSun
I think you mean:
[sql]UPDATE  people_membership pmSET  pm.membershipd_id =     IF (      pm.membership_id = 2,      6,      pm.membership_id    ),  pm.membershipd_id =     IF (      pm.membership_id = 3,      4,      pm.membership_id)[/sql]

In this case, the MySQL IF acts like a PHP ternary operator.

Also, maybe a nested IF will be better.

Re: Update Multiple Rows

Posted: Fri Jan 30, 2009 6:26 pm
by Benjamin
Ah yes, good stuff :)

Re: Update Multiple Rows

Posted: Fri Jan 30, 2009 7:02 pm
by psurrena
VladSun wrote:You have to explain in more details and give better examples, because I can't understand why you can't use a query like this:
[sql]UPDATE people_membership SET membership_id=6 WHERE membership_id=2;UPDATE people_membership SET membership_id=4 WHERE membership_id=3; [/sql]

Because If you set the new 2 to 6 and then switch the old 6 to something else it's actually changing the new one.

Re: Update Multiple Rows

Posted: Fri Jan 30, 2009 7:09 pm
by VladSun
psurrena wrote:
VladSun wrote:You have to explain in more details and give better examples, because I can't understand why you can't use a query like this:
[sql]UPDATE people_membership SET membership_id=6 WHERE membership_id=2;UPDATE people_membership SET membership_id=4 WHERE membership_id=3; [/sql]

Because If you set the new 2 to 6 and then switch the old 6 to something else it's actually changing the new one.
It's true but it will not happen in your examples - that is, you don't change records with id=6 anywhere. That's why I said "You need a better examples", right?

Maybe if you explain why do you need this in real world, I'll be more helpful.

Re: Update Multiple Rows

Posted: Sat Jan 31, 2009 8:06 pm
by psurrena
The reason was I had a list of professional members listed after peoples names, AIA, FAIA, LEED and so on. When they are pulled from the DB they need to be in order of importance and not alpha.

There is one table in my DB that has the id of the person and the id(s) of the memberships. There are hundreds of rows and that is where the question came from.

Memberships were like this:
1.AIA
2.ASLA
3.FAIA
3.FASLA
...

They ended up as:
1.FAIA
2.AIA
3.FASLA
4.ALSA