Mixing
Why CONCAT does not insert text for the first time into mySQL table?
I am using UPDATE to insert simple text into a table where the field is MEDIUMTEXT (nullable field).
It is strange that it does not work when the field is null initially. If I manually enter at least a one character/space, then it's working.
I want to append the new text into existing text in the field.
UPDATE pen SET
PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT(PEN_STATUS_CHANGE_REASON,'n',ChangeDate,':',EmployeeID,':',ChangeReason)
WHERE PEN_ID = PenID;
Why is this?
mysql sql sql-update
edited Jan 1 at 18:46
GMB
17.2k3928
asked Jan 1 at 18:17
PCGPCG
139211
add a comment |
I am using UPDATE to insert simple text into a table where the field is MEDIUMTEXT (nullable field).
It is strange that it does not work when the field is null initially. If I manually enter at least a one character/space, then it's working.
I want to append the new text into existing text in the field.
UPDATE pen SET
PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT(PEN_STATUS_CHANGE_REASON,'n',ChangeDate,':',EmployeeID,':',ChangeReason)
WHERE PEN_ID = PenID;
Why is this?
mysql sql sql-update
edited Jan 1 at 18:46
GMB
17.2k3928
asked Jan 1 at 18:17
PCGPCG
139211
add a comment |
I am using UPDATE to insert simple text into a table where the field is MEDIUMTEXT (nullable field).
It is strange that it does not work when the field is null initially. If I manually enter at least a one character/space, then it's working.
I want to append the new text into existing text in the field.
UPDATE pen SET
PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT(PEN_STATUS_CHANGE_REASON,'n',ChangeDate,':',EmployeeID,':',ChangeReason)
WHERE PEN_ID = PenID;
Why is this?
mysql sql sql-update
edited Jan 1 at 18:46
GMB
17.2k3928
asked Jan 1 at 18:17
PCGPCG
139211
I am using UPDATE to insert simple text into a table where the field is MEDIUMTEXT (nullable field).
It is strange that it does not work when the field is null initially. If I manually enter at least a one character/space, then it's working.
I want to append the new text into existing text in the field.
UPDATE pen SET
PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT(PEN_STATUS_CHANGE_REASON,'n',ChangeDate,':',EmployeeID,':',ChangeReason)
WHERE PEN_ID = PenID;
Why is this?
mysql sql sql-update
mysql sql sql-update
edited Jan 1 at 18:46
GMB
17.2k3928
asked Jan 1 at 18:17
PCGPCG
139211
edited Jan 1 at 18:46
GMB
17.2k3928
asked Jan 1 at 18:17
PCGPCG
139211
edited Jan 1 at 18:46
GMB
17.2k3928
edited Jan 1 at 18:46
GMB
17.2k3928
edited Jan 1 at 18:46
GMB
17.2k3928
17.2k3928
asked Jan 1 at 18:17
PCGPCG
139211
asked Jan 1 at 18:17
PCGPCG
139211
asked Jan 1 at 18:17
PCGPCG
139211
139211
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Presumably, because something is NULL. Try using CONCAT_WS() instead:
UPDATE pen
SET PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT_WS('n',
PEN_STATUS_CHANGE_REASON,
CONCAT_WS(':', ChangeDate, EmployeeID, ChangeReason
)
)
WHERE PEN_ID = PenID;
CONCAT_WS() ignores NULL arguments. Plus, the separator only needs to be listed once.
answered Jan 1 at 18:25
Gordon LinoffGordon Linoff
785k35310417
add a comment |
CONCAT does not handle NULL values. As explained in the MySQL manual:
CONCAT()returns NULL if any argument is NULL.
You want to use COALESCE to handle that use case, like :
UPDATE pen SET
PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT(
COALESCE(PEN_STATUS_CHANGE_REASON, ''),
'n',
ChangeDate,
':',
EmployeeID,
':',
ChangeReason
)
WHERE PEN_ID = PenID;
edited Jan 1 at 19:16
Salman A
183k66341439
answered Jan 1 at 18:23
GMBGMB
17.2k3928
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53997819%2fwhy-concat-does-not-insert-text-for-the-first-time-into-mysql-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Presumably, because something is NULL. Try using CONCAT_WS() instead:
UPDATE pen
SET PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT_WS('n',
PEN_STATUS_CHANGE_REASON,
CONCAT_WS(':', ChangeDate, EmployeeID, ChangeReason
)
)
WHERE PEN_ID = PenID;
CONCAT_WS() ignores NULL arguments. Plus, the separator only needs to be listed once.
answered Jan 1 at 18:25
Gordon LinoffGordon Linoff
785k35310417
add a comment |
Presumably, because something is NULL. Try using CONCAT_WS() instead:
UPDATE pen
SET PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT_WS('n',
PEN_STATUS_CHANGE_REASON,
CONCAT_WS(':', ChangeDate, EmployeeID, ChangeReason
)
)
WHERE PEN_ID = PenID;
CONCAT_WS() ignores NULL arguments. Plus, the separator only needs to be listed once.
answered Jan 1 at 18:25
Gordon LinoffGordon Linoff
785k35310417
add a comment |
Presumably, because something is NULL. Try using CONCAT_WS() instead:
UPDATE pen
SET PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT_WS('n',
PEN_STATUS_CHANGE_REASON,
CONCAT_WS(':', ChangeDate, EmployeeID, ChangeReason
)
)
WHERE PEN_ID = PenID;
CONCAT_WS() ignores NULL arguments. Plus, the separator only needs to be listed once.
answered Jan 1 at 18:25
Gordon LinoffGordon Linoff
785k35310417
Presumably, because something is NULL. Try using CONCAT_WS() instead:
UPDATE pen
SET PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT_WS('n',
PEN_STATUS_CHANGE_REASON,
CONCAT_WS(':', ChangeDate, EmployeeID, ChangeReason
)
)
WHERE PEN_ID = PenID;
CONCAT_WS() ignores NULL arguments. Plus, the separator only needs to be listed once.
answered Jan 1 at 18:25
Gordon LinoffGordon Linoff
785k35310417
answered Jan 1 at 18:25
Gordon LinoffGordon Linoff
785k35310417
answered Jan 1 at 18:25
Gordon LinoffGordon Linoff
785k35310417
answered Jan 1 at 18:25
Gordon LinoffGordon Linoff
785k35310417
785k35310417
add a comment |
add a comment |
CONCAT does not handle NULL values. As explained in the MySQL manual:
CONCAT()returns NULL if any argument is NULL.
You want to use COALESCE to handle that use case, like :
UPDATE pen SET
PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT(
COALESCE(PEN_STATUS_CHANGE_REASON, ''),
'n',
ChangeDate,
':',
EmployeeID,
':',
ChangeReason
)
WHERE PEN_ID = PenID;
edited Jan 1 at 19:16
Salman A
183k66341439
answered Jan 1 at 18:23
GMBGMB
17.2k3928
add a comment |
CONCAT does not handle NULL values. As explained in the MySQL manual:
CONCAT()returns NULL if any argument is NULL.
You want to use COALESCE to handle that use case, like :
UPDATE pen SET
PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT(
COALESCE(PEN_STATUS_CHANGE_REASON, ''),
'n',
ChangeDate,
':',
EmployeeID,
':',
ChangeReason
)
WHERE PEN_ID = PenID;
edited Jan 1 at 19:16
Salman A
183k66341439
answered Jan 1 at 18:23
GMBGMB
17.2k3928
add a comment |
CONCAT does not handle NULL values. As explained in the MySQL manual:
CONCAT()returns NULL if any argument is NULL.
You want to use COALESCE to handle that use case, like :
UPDATE pen SET
PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT(
COALESCE(PEN_STATUS_CHANGE_REASON, ''),
'n',
ChangeDate,
':',
EmployeeID,
':',
ChangeReason
)
WHERE PEN_ID = PenID;
edited Jan 1 at 19:16
Salman A
183k66341439
answered Jan 1 at 18:23
GMBGMB
17.2k3928
CONCAT does not handle NULL values. As explained in the MySQL manual:
CONCAT()returns NULL if any argument is NULL.
You want to use COALESCE to handle that use case, like :
UPDATE pen SET
PEN_STATUS = @PenStat,
PEN_STATUS_CHANGE_REASON = CONCAT(
COALESCE(PEN_STATUS_CHANGE_REASON, ''),
'n',
ChangeDate,
':',
EmployeeID,
':',
ChangeReason
)
WHERE PEN_ID = PenID;
edited Jan 1 at 19:16
Salman A
183k66341439
answered Jan 1 at 18:23
GMBGMB
17.2k3928
edited Jan 1 at 19:16
Salman A
183k66341439
edited Jan 1 at 19:16
Salman A
183k66341439
edited Jan 1 at 19:16
Salman A
183k66341439
183k66341439
answered Jan 1 at 18:23
GMBGMB
17.2k3928
answered Jan 1 at 18:23
GMBGMB
17.2k3928
answered Jan 1 at 18:23
GMBGMB
17.2k3928
17.2k3928
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53997819%2fwhy-concat-does-not-insert-text-for-the-first-time-into-mysql-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
