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.
精彩评论