How to make an insert, if not null result of a select in a single query?
This is what I tried to do:
INSERT INTO `table2` SET `coditem`=(SELECT `tb1`.`cod`
FROM `table1` as `tb1`
WHERE `tb1`.`cod` = '4F576DC'
AND `tb1`.`user` = 'CB78005'),
`coduser`='CB78005', `date`= NOW(),`textsdata`='2',`active`='1';
I have two tables.
One with the client's registry (table1) and another with the request for changes requested by the client (table2).
When the user makes a request, I will have the code of the item (coditem), and the code of the user (coduser) who made the request.
For security, in select (SELECT `tb1`.`cod` FROM `table1` as `tb1` WHERE `tb1`.`cod` = '4F576DC' AND `tb1`.`user` = 'CB78005') I check if the item is same as the user who is requesting for the request. For it would be easy to change this code on the form. I must prevent the client from trying to modify the item from other users.
When all is well, it works perfectly. But when the code of the item is not of the client, the select returns null and gives an error.
How can I solve this problem when return null?
When the select returns null the insert should not be done.
mysql
asked Nov 25 '18 at 4:24
Samanta SilvaSamanta Silva
1407
add a comment |
This is what I tried to do:
INSERT INTO `table2` SET `coditem`=(SELECT `tb1`.`cod`
FROM `table1` as `tb1`
WHERE `tb1`.`cod` = '4F576DC'
AND `tb1`.`user` = 'CB78005'),
`coduser`='CB78005', `date`= NOW(),`textsdata`='2',`active`='1';
I have two tables.
One with the client's registry (table1) and another with the request for changes requested by the client (table2).
When the user makes a request, I will have the code of the item (coditem), and the code of the user (coduser) who made the request.
For security, in select (SELECT `tb1`.`cod` FROM `table1` as `tb1` WHERE `tb1`.`cod` = '4F576DC' AND `tb1`.`user` = 'CB78005') I check if the item is same as the user who is requesting for the request. For it would be easy to change this code on the form. I must prevent the client from trying to modify the item from other users.
When all is well, it works perfectly. But when the code of the item is not of the client, the select returns null and gives an error.
How can I solve this problem when return null?
When the select returns null the insert should not be done.
mysql
asked Nov 25 '18 at 4:24
Samanta SilvaSamanta Silva
1407
add a comment |
This is what I tried to do:
INSERT INTO `table2` SET `coditem`=(SELECT `tb1`.`cod`
FROM `table1` as `tb1`
WHERE `tb1`.`cod` = '4F576DC'
AND `tb1`.`user` = 'CB78005'),
`coduser`='CB78005', `date`= NOW(),`textsdata`='2',`active`='1';
I have two tables.
One with the client's registry (table1) and another with the request for changes requested by the client (table2).
When the user makes a request, I will have the code of the item (coditem), and the code of the user (coduser) who made the request.
For security, in select (SELECT `tb1`.`cod` FROM `table1` as `tb1` WHERE `tb1`.`cod` = '4F576DC' AND `tb1`.`user` = 'CB78005') I check if the item is same as the user who is requesting for the request. For it would be easy to change this code on the form. I must prevent the client from trying to modify the item from other users.
When all is well, it works perfectly. But when the code of the item is not of the client, the select returns null and gives an error.
How can I solve this problem when return null?
When the select returns null the insert should not be done.
mysql
asked Nov 25 '18 at 4:24
Samanta SilvaSamanta Silva
1407
This is what I tried to do:
INSERT INTO `table2` SET `coditem`=(SELECT `tb1`.`cod`
FROM `table1` as `tb1`
WHERE `tb1`.`cod` = '4F576DC'
AND `tb1`.`user` = 'CB78005'),
`coduser`='CB78005', `date`= NOW(),`textsdata`='2',`active`='1';
I have two tables.
One with the client's registry (table1) and another with the request for changes requested by the client (table2).
When the user makes a request, I will have the code of the item (coditem), and the code of the user (coduser) who made the request.
For security, in select (SELECT `tb1`.`cod` FROM `table1` as `tb1` WHERE `tb1`.`cod` = '4F576DC' AND `tb1`.`user` = 'CB78005') I check if the item is same as the user who is requesting for the request. For it would be easy to change this code on the form. I must prevent the client from trying to modify the item from other users.
When all is well, it works perfectly. But when the code of the item is not of the client, the select returns null and gives an error.
How can I solve this problem when return null?
When the select returns null the insert should not be done.
mysql
mysql
asked Nov 25 '18 at 4:24
Samanta SilvaSamanta Silva
1407
asked Nov 25 '18 at 4:24
Samanta SilvaSamanta Silva
1407
asked Nov 25 '18 at 4:24
Samanta SilvaSamanta Silva
1407
asked Nov 25 '18 at 4:24
Samanta SilvaSamanta Silva
1407
asked Nov 25 '18 at 4:24
Samanta SilvaSamanta Silva
1407
1407
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Try rewriting your insert as a pure INSERT INTO ... SELECT:
INSERT INTO table2 (coditem, coduser, date, textsdata, active)
SELECT t1.cod, 'CB78005', NOW(), '2', '1'
FROM table1 t1
WHERE
t1.cod = '4F576DC' AND
t1.user = 'CB78005';
As written above, if the cod value were NULL, no insert at all would happen. There is probably a better way to express your logic, but this might fix your immediate problem.
answered Nov 25 '18 at 4:31
Tim BiegeleisenTim Biegeleisen
228k1394147
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%2f53464636%2fhow-to-make-an-insert-if-not-null-result-of-a-select-in-a-single-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try rewriting your insert as a pure INSERT INTO ... SELECT:
INSERT INTO table2 (coditem, coduser, date, textsdata, active)
SELECT t1.cod, 'CB78005', NOW(), '2', '1'
FROM table1 t1
WHERE
t1.cod = '4F576DC' AND
t1.user = 'CB78005';
As written above, if the cod value were NULL, no insert at all would happen. There is probably a better way to express your logic, but this might fix your immediate problem.
answered Nov 25 '18 at 4:31
Tim BiegeleisenTim Biegeleisen
228k1394147
add a comment |
Try rewriting your insert as a pure INSERT INTO ... SELECT:
INSERT INTO table2 (coditem, coduser, date, textsdata, active)
SELECT t1.cod, 'CB78005', NOW(), '2', '1'
FROM table1 t1
WHERE
t1.cod = '4F576DC' AND
t1.user = 'CB78005';
As written above, if the cod value were NULL, no insert at all would happen. There is probably a better way to express your logic, but this might fix your immediate problem.
answered Nov 25 '18 at 4:31
Tim BiegeleisenTim Biegeleisen
228k1394147
add a comment |
Try rewriting your insert as a pure INSERT INTO ... SELECT:
INSERT INTO table2 (coditem, coduser, date, textsdata, active)
SELECT t1.cod, 'CB78005', NOW(), '2', '1'
FROM table1 t1
WHERE
t1.cod = '4F576DC' AND
t1.user = 'CB78005';
As written above, if the cod value were NULL, no insert at all would happen. There is probably a better way to express your logic, but this might fix your immediate problem.
answered Nov 25 '18 at 4:31
Tim BiegeleisenTim Biegeleisen
228k1394147
Try rewriting your insert as a pure INSERT INTO ... SELECT:
INSERT INTO table2 (coditem, coduser, date, textsdata, active)
SELECT t1.cod, 'CB78005', NOW(), '2', '1'
FROM table1 t1
WHERE
t1.cod = '4F576DC' AND
t1.user = 'CB78005';
As written above, if the cod value were NULL, no insert at all would happen. There is probably a better way to express your logic, but this might fix your immediate problem.
answered Nov 25 '18 at 4:31
Tim BiegeleisenTim Biegeleisen
228k1394147
answered Nov 25 '18 at 4:31
Tim BiegeleisenTim Biegeleisen
228k1394147
answered Nov 25 '18 at 4:31
Tim BiegeleisenTim Biegeleisen
228k1394147
answered Nov 25 '18 at 4:31
Tim BiegeleisenTim Biegeleisen
228k1394147
228k1394147
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%2f53464636%2fhow-to-make-an-insert-if-not-null-result-of-a-select-in-a-single-query%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
