开发者

Insert Record or Update Record If Exists in Table In One Query in MySQL

My Table

member_id - profil_id - A - B - C
1           2           1   0   0
1           3           0   1   0

I want to update record for (member_id=1 and profil_id=2 and A=1)

member_id - profil_id - A - B - C
1           2           2   0   0
1           3           0   1   0

开发者_开发问答and again, I want to update record for (member_id=1 and profil_id=2 and A=1)

member_id - profil_id - A - B - C
1           2           3   0   0
1           3           0   1   0

I want to insert record for (member_id=1 and profil_id=4 and A=1)

member_id - profil_id - A - B - C
1           2           3   0   0
1           3           0   1   0
1           4           1   0   0

and again I want to update record for (member_id=1 and profil_id=4 and C=1)

member_id - profil_id - A - B - C
1           2           3   0   0
1           3           0   1   0
1           4           1   0   1

and again I want to update record for (member_id=1 and profil_id=4 and C=1)

member_id - profil_id - A - B - C
1           2           3   0   0
1           3           0   1   0
1           4           1   0   2

like this...

thanks..


There are two ways of doing this in MySQL. The first is using REPLACE. The second is using INSERT...ON DUPLICATE KEY UPDATE.

REPLACE will try a delete row, and regardless of success or failure, insert the new row.

INSERT...ON DUPLICATE KEY UPDATE will try and insert a row and if the insert fails due to a duplicate key on an index error, does an update.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜