Best practice to update a row if exists otherwise insert [duplicate]

This question already has an answer here:

Insert into a MySQL table or update if exists

10 answers

I want to know more efficient way to update a row in table, if row not exists it must be inserted.

My query is

UPDATE MyGuests SET lastname='Doe' WHERE id=2

When this query is run and there is no row with id=2, a row must be inserted like

INSERT INTO MyGuests (lastname, id)
VALUES ('Doe', 2)

Note:

My primary intention is to update the row

I don't want primary key change when updating a row.

asked Nov 15 '18 at 4:26

DMP

13210

marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
Nov 15 '18 at 11:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

id is not primary key but it is UNIQUE

– DMP
Nov 15 '18 at 4:34

add a comment |

This question already has an answer here:

Insert into a MySQL table or update if exists

10 answers

I want to know more efficient way to update a row in table, if row not exists it must be inserted.

My query is

UPDATE MyGuests SET lastname='Doe' WHERE id=2

When this query is run and there is no row with id=2, a row must be inserted like

INSERT INTO MyGuests (lastname, id)
VALUES ('Doe', 2)

Note:

My primary intention is to update the row

I don't want primary key change when updating a row.

asked Nov 15 '18 at 4:26

DMP

13210

marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
Nov 15 '18 at 11:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

id is not primary key but it is UNIQUE

– DMP
Nov 15 '18 at 4:34

add a comment |

This question already has an answer here:

Insert into a MySQL table or update if exists

10 answers

I want to know more efficient way to update a row in table, if row not exists it must be inserted.

My query is

UPDATE MyGuests SET lastname='Doe' WHERE id=2

When this query is run and there is no row with id=2, a row must be inserted like

INSERT INTO MyGuests (lastname, id)
VALUES ('Doe', 2)

Note:

My primary intention is to update the row

I don't want primary key change when updating a row.

asked Nov 15 '18 at 4:26

DMP

13210

This question already has an answer here:

Insert into a MySQL table or update if exists

10 answers

I want to know more efficient way to update a row in table, if row not exists it must be inserted.

My query is

UPDATE MyGuests SET lastname='Doe' WHERE id=2

When this query is run and there is no row with id=2, a row must be inserted like

INSERT INTO MyGuests (lastname, id)
VALUES ('Doe', 2)

Note:

My primary intention is to update the row

I don't want primary key change when updating a row.

This question already has an answer here:

Insert into a MySQL table or update if exists

10 answers

php mysql mysqli

asked Nov 15 '18 at 4:26

DMP

13210

asked Nov 15 '18 at 4:26

DMP

13210

asked Nov 15 '18 at 4:26

DMP

13210

asked Nov 15 '18 at 4:26

DMP

13210

asked Nov 15 '18 at 4:26

DMP

13210

marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
Nov 15 '18 at 11:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
Nov 15 '18 at 11:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

id is not primary key but it is UNIQUE

– DMP
Nov 15 '18 at 4:34

add a comment |

id is not primary key but it is UNIQUE

– DMP
Nov 15 '18 at 4:34

id is not primary key but it is UNIQUE

– DMP
Nov 15 '18 at 4:34

add a comment |

2 Answers
2

active

oldest

votes

Assuming that id is your Primary/Unique Key, you can use INSERT..ON DUPLICATE KEY UPDATE:

INSERT INTO MyGuests (id, lastname) VALUES (2, 'Doe')
 ON DUPLICATE KEY UPDATE lastname = 'Doe'

answered Nov 15 '18 at 4:31

Madhur Bhaiya

19.6k62236

add a comment |

You can refer this way:

UPDATE MyGuests SET lastname='Doe' WHERE id=2
IF ROW_COUNT()=0
 INSERT INTO MyGuests (lastname, id) VALUES ('Doe',2)

answered Nov 15 '18 at 7:06

thanhdung0312

30627

add a comment |

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

Assuming that id is your Primary/Unique Key, you can use INSERT..ON DUPLICATE KEY UPDATE:

INSERT INTO MyGuests (id, lastname) VALUES (2, 'Doe')
 ON DUPLICATE KEY UPDATE lastname = 'Doe'

answered Nov 15 '18 at 4:31

Madhur Bhaiya

19.6k62236

add a comment |

Assuming that id is your Primary/Unique Key, you can use INSERT..ON DUPLICATE KEY UPDATE:

INSERT INTO MyGuests (id, lastname) VALUES (2, 'Doe')
 ON DUPLICATE KEY UPDATE lastname = 'Doe'

answered Nov 15 '18 at 4:31

Madhur Bhaiya

19.6k62236

add a comment |

Assuming that id is your Primary/Unique Key, you can use INSERT..ON DUPLICATE KEY UPDATE:

INSERT INTO MyGuests (id, lastname) VALUES (2, 'Doe')
 ON DUPLICATE KEY UPDATE lastname = 'Doe'

answered Nov 15 '18 at 4:31

Madhur Bhaiya

19.6k62236

Assuming that id is your Primary/Unique Key, you can use INSERT..ON DUPLICATE KEY UPDATE:

INSERT INTO MyGuests (id, lastname) VALUES (2, 'Doe')
 ON DUPLICATE KEY UPDATE lastname = 'Doe'

answered Nov 15 '18 at 4:31

Madhur Bhaiya

19.6k62236

answered Nov 15 '18 at 4:31

Madhur Bhaiya

19.6k62236

answered Nov 15 '18 at 4:31

Madhur Bhaiya

19.6k62236

answered Nov 15 '18 at 4:31

Madhur Bhaiya

19.6k62236

add a comment |

You can refer this way:

UPDATE MyGuests SET lastname='Doe' WHERE id=2
IF ROW_COUNT()=0
 INSERT INTO MyGuests (lastname, id) VALUES ('Doe',2)

answered Nov 15 '18 at 7:06

thanhdung0312

30627

add a comment |

You can refer this way:

UPDATE MyGuests SET lastname='Doe' WHERE id=2
IF ROW_COUNT()=0
 INSERT INTO MyGuests (lastname, id) VALUES ('Doe',2)

answered Nov 15 '18 at 7:06

thanhdung0312

30627

add a comment |

You can refer this way:

UPDATE MyGuests SET lastname='Doe' WHERE id=2
IF ROW_COUNT()=0
 INSERT INTO MyGuests (lastname, id) VALUES ('Doe',2)

answered Nov 15 '18 at 7:06

thanhdung0312

30627

You can refer this way:

UPDATE MyGuests SET lastname='Doe' WHERE id=2
IF ROW_COUNT()=0
 INSERT INTO MyGuests (lastname, id) VALUES ('Doe',2)

answered Nov 15 '18 at 7:06

thanhdung0312

30627

answered Nov 15 '18 at 7:06

thanhdung0312

30627

answered Nov 15 '18 at 7:06

thanhdung0312

30627

answered Nov 15 '18 at 7:06

thanhdung0312

30627

add a comment |

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Myujth