Mixing
joining in an update in db2
I'm getting an error when trying an update with a join in db2 that 'inner' is not expected and that it is instead expecting 'set'.
Looking at the query I understand what it is saying but I'm confused: Can you not do a join in an update on DB2?
I'm usually using MySQL but this script has to run against a db2 database and I've never come across this issue but it seems like it should be correct using db2 for iseries 7.2
UPDATE data d
inner join sales s
ON d.sku_id = s.id
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
WHERE d.custID = cast(:cust as int)
sql db2
asked Nov 20 '18 at 14:22
Tom N.Tom N.
1,302414
add a comment |
I'm getting an error when trying an update with a join in db2 that 'inner' is not expected and that it is instead expecting 'set'.
Looking at the query I understand what it is saying but I'm confused: Can you not do a join in an update on DB2?
I'm usually using MySQL but this script has to run against a db2 database and I've never come across this issue but it seems like it should be correct using db2 for iseries 7.2
UPDATE data d
inner join sales s
ON d.sku_id = s.id
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
WHERE d.custID = cast(:cust as int)
sql db2
asked Nov 20 '18 at 14:22
Tom N.Tom N.
1,302414
Use the MERGE statement instead.
– mao
Nov 20 '18 at 14:57
Possible duplicate of How to update DB2 table with a join?
– mao
Nov 20 '18 at 14:58
I looked at it but the format looks a little different. How exactly would I use a merge on this? i've only used a merge for upserts before
– Tom N.
Nov 20 '18 at 15:44
Possible duplicate of SQL update from one Table to another based on a ID match IN db2
– mustaccio
Nov 20 '18 at 16:52
I posted my IRL answer of SQL that works on a variety of DB flavors
– danny117
Nov 20 '18 at 17:19
add a comment |
I'm getting an error when trying an update with a join in db2 that 'inner' is not expected and that it is instead expecting 'set'.
Looking at the query I understand what it is saying but I'm confused: Can you not do a join in an update on DB2?
I'm usually using MySQL but this script has to run against a db2 database and I've never come across this issue but it seems like it should be correct using db2 for iseries 7.2
UPDATE data d
inner join sales s
ON d.sku_id = s.id
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
WHERE d.custID = cast(:cust as int)
sql db2
asked Nov 20 '18 at 14:22
Tom N.Tom N.
1,302414
I'm getting an error when trying an update with a join in db2 that 'inner' is not expected and that it is instead expecting 'set'.
Looking at the query I understand what it is saying but I'm confused: Can you not do a join in an update on DB2?
I'm usually using MySQL but this script has to run against a db2 database and I've never come across this issue but it seems like it should be correct using db2 for iseries 7.2
UPDATE data d
inner join sales s
ON d.sku_id = s.id
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
WHERE d.custID = cast(:cust as int)
sql db2
sql db2
asked Nov 20 '18 at 14:22
Tom N.Tom N.
1,302414
asked Nov 20 '18 at 14:22
Tom N.Tom N.
1,302414
asked Nov 20 '18 at 14:22
Tom N.Tom N.
1,302414
asked Nov 20 '18 at 14:22
Tom N.Tom N.
1,302414
asked Nov 20 '18 at 14:22
Tom N.Tom N.
1,302414
1,302414
Use the MERGE statement instead.
– mao
Nov 20 '18 at 14:57
Possible duplicate of How to update DB2 table with a join?
– mao
Nov 20 '18 at 14:58
I looked at it but the format looks a little different. How exactly would I use a merge on this? i've only used a merge for upserts before
– Tom N.
Nov 20 '18 at 15:44
Possible duplicate of SQL update from one Table to another based on a ID match IN db2
– mustaccio
Nov 20 '18 at 16:52
I posted my IRL answer of SQL that works on a variety of DB flavors
– danny117
Nov 20 '18 at 17:19
add a comment |
Use the MERGE statement instead.
– mao
Nov 20 '18 at 14:57
Possible duplicate of How to update DB2 table with a join?
– mao
Nov 20 '18 at 14:58
I looked at it but the format looks a little different. How exactly would I use a merge on this? i've only used a merge for upserts before
– Tom N.
Nov 20 '18 at 15:44
Possible duplicate of SQL update from one Table to another based on a ID match IN db2
– mustaccio
Nov 20 '18 at 16:52
I posted my IRL answer of SQL that works on a variety of DB flavors
– danny117
Nov 20 '18 at 17:19
Use the MERGE statement instead.
– mao
Nov 20 '18 at 14:57
Use the MERGE statement instead.
– mao
Nov 20 '18 at 14:57
Possible duplicate of How to update DB2 table with a join?
– mao
Nov 20 '18 at 14:58
Possible duplicate of How to update DB2 table with a join?
– mao
Nov 20 '18 at 14:58
I looked at it but the format looks a little different. How exactly would I use a merge on this? i've only used a merge for upserts before
– Tom N.
Nov 20 '18 at 15:44
I looked at it but the format looks a little different. How exactly would I use a merge on this? i've only used a merge for upserts before
– Tom N.
Nov 20 '18 at 15:44
Possible duplicate of SQL update from one Table to another based on a ID match IN db2
– mustaccio
Nov 20 '18 at 16:52
Possible duplicate of SQL update from one Table to another based on a ID match IN db2
– mustaccio
Nov 20 '18 at 16:52
I posted my IRL answer of SQL that works on a variety of DB flavors
– danny117
Nov 20 '18 at 17:19
I posted my IRL answer of SQL that works on a variety of DB flavors
– danny117
Nov 20 '18 at 17:19
add a comment |
2 Answers
2
active
oldest
votes
Exists clause can be used on DB2 for Iseries update statement. I just took the join and moved it to a where exists clause. Your only updating one table with supplied values so this was easy transformation.
UPDATE data d
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
WHERE d.custID = cast(:cust as int)
and exists (
select s.id from
sales s
where d.sku_id = s.id
)
answered Nov 20 '18 at 17:18
danny117danny117
5,43611933
add a comment |
I think you can use a from clause in DB2:
UPDATE data d
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
FROM sales s
WHERE d.sku_id = s.id AND
d.custID = cast(:cust as int);
answered Nov 20 '18 at 15:00
Gordon LinoffGordon Linoff
767k35300402
I thought so too for some reason, but it says keyword FROM is not expected
– Tom N.
Nov 20 '18 at 15:33
@TomN. . . . Alas, that probably depends on the version of DB2.
– Gordon Linoff
Nov 20 '18 at 18:40
Yeah I've learned it can be a bit picky lol. Thanks!
– Tom N.
Nov 20 '18 at 18:41
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%2f53395084%2fjoining-in-an-update-in-db2%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
Exists clause can be used on DB2 for Iseries update statement. I just took the join and moved it to a where exists clause. Your only updating one table with supplied values so this was easy transformation.
UPDATE data d
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
WHERE d.custID = cast(:cust as int)
and exists (
select s.id from
sales s
where d.sku_id = s.id
)
answered Nov 20 '18 at 17:18
danny117danny117
5,43611933
add a comment |
Exists clause can be used on DB2 for Iseries update statement. I just took the join and moved it to a where exists clause. Your only updating one table with supplied values so this was easy transformation.
UPDATE data d
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
WHERE d.custID = cast(:cust as int)
and exists (
select s.id from
sales s
where d.sku_id = s.id
)
answered Nov 20 '18 at 17:18
danny117danny117
5,43611933
add a comment |
Exists clause can be used on DB2 for Iseries update statement. I just took the join and moved it to a where exists clause. Your only updating one table with supplied values so this was easy transformation.
UPDATE data d
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
WHERE d.custID = cast(:cust as int)
and exists (
select s.id from
sales s
where d.sku_id = s.id
)
answered Nov 20 '18 at 17:18
danny117danny117
5,43611933
Exists clause can be used on DB2 for Iseries update statement. I just took the join and moved it to a where exists clause. Your only updating one table with supplied values so this was easy transformation.
UPDATE data d
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
WHERE d.custID = cast(:cust as int)
and exists (
select s.id from
sales s
where d.sku_id = s.id
)
answered Nov 20 '18 at 17:18
danny117danny117
5,43611933
answered Nov 20 '18 at 17:18
danny117danny117
5,43611933
answered Nov 20 '18 at 17:18
danny117danny117
5,43611933
answered Nov 20 '18 at 17:18
danny117danny117
5,43611933
5,43611933
add a comment |
add a comment |
I think you can use a from clause in DB2:
UPDATE data d
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
FROM sales s
WHERE d.sku_id = s.id AND
d.custID = cast(:cust as int);
answered Nov 20 '18 at 15:00
Gordon LinoffGordon Linoff
767k35300402
I thought so too for some reason, but it says keyword FROM is not expected
– Tom N.
Nov 20 '18 at 15:33
@TomN. . . . Alas, that probably depends on the version of DB2.
– Gordon Linoff
Nov 20 '18 at 18:40
Yeah I've learned it can be a bit picky lol. Thanks!
– Tom N.
Nov 20 '18 at 18:41
add a comment |
I think you can use a from clause in DB2:
UPDATE data d
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
FROM sales s
WHERE d.sku_id = s.id AND
d.custID = cast(:cust as int);
answered Nov 20 '18 at 15:00
Gordon LinoffGordon Linoff
767k35300402
I thought so too for some reason, but it says keyword FROM is not expected
– Tom N.
Nov 20 '18 at 15:33
@TomN. . . . Alas, that probably depends on the version of DB2.
– Gordon Linoff
Nov 20 '18 at 18:40
Yeah I've learned it can be a bit picky lol. Thanks!
– Tom N.
Nov 20 '18 at 18:41
add a comment |
I think you can use a from clause in DB2:
UPDATE data d
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
FROM sales s
WHERE d.sku_id = s.id AND
d.custID = cast(:cust as int);
answered Nov 20 '18 at 15:00
Gordon LinoffGordon Linoff
767k35300402
I think you can use a from clause in DB2:
UPDATE data d
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
quantity = cast(:QUANTITY as int)
FROM sales s
WHERE d.sku_id = s.id AND
d.custID = cast(:cust as int);
answered Nov 20 '18 at 15:00
Gordon LinoffGordon Linoff
767k35300402
answered Nov 20 '18 at 15:00
Gordon LinoffGordon Linoff
767k35300402
answered Nov 20 '18 at 15:00
Gordon LinoffGordon Linoff
767k35300402
answered Nov 20 '18 at 15:00
Gordon LinoffGordon Linoff
767k35300402
767k35300402
I thought so too for some reason, but it says keyword FROM is not expected
– Tom N.
Nov 20 '18 at 15:33
@TomN. . . . Alas, that probably depends on the version of DB2.
– Gordon Linoff
Nov 20 '18 at 18:40
Yeah I've learned it can be a bit picky lol. Thanks!
– Tom N.
Nov 20 '18 at 18:41
add a comment |
I thought so too for some reason, but it says keyword FROM is not expected
– Tom N.
Nov 20 '18 at 15:33
@TomN. . . . Alas, that probably depends on the version of DB2.
– Gordon Linoff
Nov 20 '18 at 18:40
Yeah I've learned it can be a bit picky lol. Thanks!
– Tom N.
Nov 20 '18 at 18:41
I thought so too for some reason, but it says keyword FROM is not expected
– Tom N.
Nov 20 '18 at 15:33
I thought so too for some reason, but it says keyword FROM is not expected
– Tom N.
Nov 20 '18 at 15:33
@TomN. . . . Alas, that probably depends on the version of DB2.
– Gordon Linoff
Nov 20 '18 at 18:40
@TomN. . . . Alas, that probably depends on the version of DB2.
– Gordon Linoff
Nov 20 '18 at 18:40
Yeah I've learned it can be a bit picky lol. Thanks!
– Tom N.
Nov 20 '18 at 18:41
Yeah I've learned it can be a bit picky lol. Thanks!
– Tom N.
Nov 20 '18 at 18:41
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%2f53395084%2fjoining-in-an-update-in-db2%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
Use the MERGE statement instead.
– mao
Nov 20 '18 at 14:57
Possible duplicate of How to update DB2 table with a join?
– mao
Nov 20 '18 at 14:58
I looked at it but the format looks a little different. How exactly would I use a merge on this? i've only used a merge for upserts before
– Tom N.
Nov 20 '18 at 15:44
Possible duplicate of SQL update from one Table to another based on a ID match IN db2
– mustaccio
Nov 20 '18 at 16:52
I posted my IRL answer of SQL that works on a variety of DB flavors
– danny117
Nov 20 '18 at 17:19