Update Multiple Rows
Moderator: General Moderators
Update Multiple Rows
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]
[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
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]
[sql]UPDATE people_membership SET membership_id=6 WHERE membership_id=2;UPDATE people_membership SET membership_id=4 WHERE membership_id=3; [/sql]
There are 10 types of people in this world, those who understand binary and those who don't
Re: Update Multiple Rows
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
Did you try itastions 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 )
There are 10 types of people in this world, those who understand binary and those who don't
Re: Update Multiple Rows
Yes there are no parse errors.
Re: Update Multiple Rows
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.
[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.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Update Multiple Rows
Ah yes, good stuff 
Re: Update Multiple Rows
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
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?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.
Maybe if you explain why do you need this in real world, I'll be more helpful.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Update Multiple Rows
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
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