Mixing
Update table using join SQL Oracle
I need to update several columns of a table, but one piece of information is stored in another table, so I'm assuming I need to join them but I'm unsure how to. Basically I need to update the job title where the office is T06 and the start date where the original start_date was '05-FEB-09, 08:00' for the staff whose last name is Parker
So far I have:
UPDATE JOB
SET JOB_TITLE = 'Head of Technology'
WHERE OFFICE = 'T06'
AND SET START_DATE = '26-JUN-17, 08:00'
WHERE START_DATE = '05-FEB-09, 08:00'
FROM JOB
JOIN STAFF
ON JOB.STAFF_ID = STAFF.STAFF_ID
WHERE STAFF.LAST_NAME = 'Parker';
sql oracle join
asked Jan 1 at 14:01
The_BearThe_Bear
165
add a comment |
I need to update several columns of a table, but one piece of information is stored in another table, so I'm assuming I need to join them but I'm unsure how to. Basically I need to update the job title where the office is T06 and the start date where the original start_date was '05-FEB-09, 08:00' for the staff whose last name is Parker
So far I have:
UPDATE JOB
SET JOB_TITLE = 'Head of Technology'
WHERE OFFICE = 'T06'
AND SET START_DATE = '26-JUN-17, 08:00'
WHERE START_DATE = '05-FEB-09, 08:00'
FROM JOB
JOIN STAFF
ON JOB.STAFF_ID = STAFF.STAFF_ID
WHERE STAFF.LAST_NAME = 'Parker';
sql oracle join
asked Jan 1 at 14:01
The_BearThe_Bear
165
Please show what tables the columns are coming from.
– Gordon Linoff
Jan 1 at 14:13
add a comment |
I need to update several columns of a table, but one piece of information is stored in another table, so I'm assuming I need to join them but I'm unsure how to. Basically I need to update the job title where the office is T06 and the start date where the original start_date was '05-FEB-09, 08:00' for the staff whose last name is Parker
So far I have:
UPDATE JOB
SET JOB_TITLE = 'Head of Technology'
WHERE OFFICE = 'T06'
AND SET START_DATE = '26-JUN-17, 08:00'
WHERE START_DATE = '05-FEB-09, 08:00'
FROM JOB
JOIN STAFF
ON JOB.STAFF_ID = STAFF.STAFF_ID
WHERE STAFF.LAST_NAME = 'Parker';
sql oracle join
asked Jan 1 at 14:01
The_BearThe_Bear
165
I need to update several columns of a table, but one piece of information is stored in another table, so I'm assuming I need to join them but I'm unsure how to. Basically I need to update the job title where the office is T06 and the start date where the original start_date was '05-FEB-09, 08:00' for the staff whose last name is Parker
So far I have:
UPDATE JOB
SET JOB_TITLE = 'Head of Technology'
WHERE OFFICE = 'T06'
AND SET START_DATE = '26-JUN-17, 08:00'
WHERE START_DATE = '05-FEB-09, 08:00'
FROM JOB
JOIN STAFF
ON JOB.STAFF_ID = STAFF.STAFF_ID
WHERE STAFF.LAST_NAME = 'Parker';
sql oracle join
sql oracle join
asked Jan 1 at 14:01
The_BearThe_Bear
165
asked Jan 1 at 14:01
The_BearThe_Bear
165
asked Jan 1 at 14:01
The_BearThe_Bear
165
asked Jan 1 at 14:01
The_BearThe_Bear
165
asked Jan 1 at 14:01
The_BearThe_Bear
165
165
Please show what tables the columns are coming from.
– Gordon Linoff
Jan 1 at 14:13
add a comment |
Please show what tables the columns are coming from.
– Gordon Linoff
Jan 1 at 14:13
Please show what tables the columns are coming from.
– Gordon Linoff
Jan 1 at 14:13
Please show what tables the columns are coming from.
– Gordon Linoff
Jan 1 at 14:13
add a comment |
3 Answers
3
active
oldest
votes
Correct syntax would be
update job
set job_title = 'Head of Technology',
start_date = '26-JUN-17, 08:00'
where office = 'T06'
and start_date = '05-FEB-09, 08:00'
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
However, it might need to be modified (START_DATE condition is strange).
Also, it is unclear what START_DATE datatype is. Should be DATE, but your code suggests a string (VARCHAR2).
Alternative #1 (note TO_DATE) function:
update job
set job_title = 'Head of Technology',
start_date = to_date('26-JUN-17, 08:00', 'dd-mon-yy, hh24:mi')
where office = 'T06'
and start_date = to_date('05-FEB-09, 08:00', 'dd-mon-yy, hh24:mi')
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
Alternative #2 (note CASE and absence of START_DATE in WHERE clause):
update job
set job_title = 'Head of Technology',
start_date = case when start_date = to_date('05-FEB-09, 08:00', 'dd-mon-yy, hh24:mi')
then to_date('26-JUN-17, 08:00', 'dd-mon-yy, hh24:mi')
else start_date
end
where office = 'T06'
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
answered Jan 1 at 14:08
LittlefootLittlefoot
23.8k71534
add a comment |
I would use EXISTS:
UPDATE JOB
SET JOB_TITLE = 'Head of Technology'
OFFICE = 'T06',
START_DATE = '26-JUN-17, 08:00'
WHERE START_DATE = '05-FEB-09, 08:00' AND
OFFICE = 'T06' AND
EXISTS (SELECT 1
FROM STAFF
WHERE JOB.STAFF_ID = STAFF.STAFF_ID AND
STAFF.LAST_NAME = 'Parker'
);
answered Jan 1 at 14:14
Gordon LinoffGordon Linoff
784k35310415
add a comment |
The most native way is to use updatable join view
Simple make you join and select all relevant columns, than add UPDATE modifying the join.
update (
select job.*
FROM JOB
JOIN STAFF
ON JOB.STAFF_ID = STAFF.STAFF_ID
WHERE STAFF.LAST_NAME = 'Parker')
set JOB_TITLE = 'Head of Technology',
START_DATE = '26-JUN-17, 08:00'
The only requirement is that the column STAFF_ID in STAFF table is backed with a unique index (e.g. primary key).
Without this you get the ERROR ORA-01779: cannot modify a column which maps to a non key-preserved table
This way of UPDATE is very usefull if you need to update with a value from the joined table (not your case).
answered Jan 1 at 16:13
Marmite BomberMarmite Bomber
8,07231034
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%2f53996087%2fupdate-table-using-join-sql-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Correct syntax would be
update job
set job_title = 'Head of Technology',
start_date = '26-JUN-17, 08:00'
where office = 'T06'
and start_date = '05-FEB-09, 08:00'
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
However, it might need to be modified (START_DATE condition is strange).
Also, it is unclear what START_DATE datatype is. Should be DATE, but your code suggests a string (VARCHAR2).
Alternative #1 (note TO_DATE) function:
update job
set job_title = 'Head of Technology',
start_date = to_date('26-JUN-17, 08:00', 'dd-mon-yy, hh24:mi')
where office = 'T06'
and start_date = to_date('05-FEB-09, 08:00', 'dd-mon-yy, hh24:mi')
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
Alternative #2 (note CASE and absence of START_DATE in WHERE clause):
update job
set job_title = 'Head of Technology',
start_date = case when start_date = to_date('05-FEB-09, 08:00', 'dd-mon-yy, hh24:mi')
then to_date('26-JUN-17, 08:00', 'dd-mon-yy, hh24:mi')
else start_date
end
where office = 'T06'
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
answered Jan 1 at 14:08
LittlefootLittlefoot
23.8k71534
add a comment |
Correct syntax would be
update job
set job_title = 'Head of Technology',
start_date = '26-JUN-17, 08:00'
where office = 'T06'
and start_date = '05-FEB-09, 08:00'
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
However, it might need to be modified (START_DATE condition is strange).
Also, it is unclear what START_DATE datatype is. Should be DATE, but your code suggests a string (VARCHAR2).
Alternative #1 (note TO_DATE) function:
update job
set job_title = 'Head of Technology',
start_date = to_date('26-JUN-17, 08:00', 'dd-mon-yy, hh24:mi')
where office = 'T06'
and start_date = to_date('05-FEB-09, 08:00', 'dd-mon-yy, hh24:mi')
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
Alternative #2 (note CASE and absence of START_DATE in WHERE clause):
update job
set job_title = 'Head of Technology',
start_date = case when start_date = to_date('05-FEB-09, 08:00', 'dd-mon-yy, hh24:mi')
then to_date('26-JUN-17, 08:00', 'dd-mon-yy, hh24:mi')
else start_date
end
where office = 'T06'
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
answered Jan 1 at 14:08
LittlefootLittlefoot
23.8k71534
add a comment |
Correct syntax would be
update job
set job_title = 'Head of Technology',
start_date = '26-JUN-17, 08:00'
where office = 'T06'
and start_date = '05-FEB-09, 08:00'
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
However, it might need to be modified (START_DATE condition is strange).
Also, it is unclear what START_DATE datatype is. Should be DATE, but your code suggests a string (VARCHAR2).
Alternative #1 (note TO_DATE) function:
update job
set job_title = 'Head of Technology',
start_date = to_date('26-JUN-17, 08:00', 'dd-mon-yy, hh24:mi')
where office = 'T06'
and start_date = to_date('05-FEB-09, 08:00', 'dd-mon-yy, hh24:mi')
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
Alternative #2 (note CASE and absence of START_DATE in WHERE clause):
update job
set job_title = 'Head of Technology',
start_date = case when start_date = to_date('05-FEB-09, 08:00', 'dd-mon-yy, hh24:mi')
then to_date('26-JUN-17, 08:00', 'dd-mon-yy, hh24:mi')
else start_date
end
where office = 'T06'
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
answered Jan 1 at 14:08
LittlefootLittlefoot
23.8k71534
Correct syntax would be
update job
set job_title = 'Head of Technology',
start_date = '26-JUN-17, 08:00'
where office = 'T06'
and start_date = '05-FEB-09, 08:00'
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
However, it might need to be modified (START_DATE condition is strange).
Also, it is unclear what START_DATE datatype is. Should be DATE, but your code suggests a string (VARCHAR2).
Alternative #1 (note TO_DATE) function:
update job
set job_title = 'Head of Technology',
start_date = to_date('26-JUN-17, 08:00', 'dd-mon-yy, hh24:mi')
where office = 'T06'
and start_date = to_date('05-FEB-09, 08:00', 'dd-mon-yy, hh24:mi')
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
Alternative #2 (note CASE and absence of START_DATE in WHERE clause):
update job
set job_title = 'Head of Technology',
start_date = case when start_date = to_date('05-FEB-09, 08:00', 'dd-mon-yy, hh24:mi')
then to_date('26-JUN-17, 08:00', 'dd-mon-yy, hh24:mi')
else start_date
end
where office = 'T06'
and staff_id =(select staff_id
from staff
where last_name = 'Parker'
);
answered Jan 1 at 14:08
LittlefootLittlefoot
23.8k71534
answered Jan 1 at 14:08
LittlefootLittlefoot
23.8k71534
answered Jan 1 at 14:08
LittlefootLittlefoot
23.8k71534
answered Jan 1 at 14:08
LittlefootLittlefoot
23.8k71534
23.8k71534
add a comment |
add a comment |
I would use EXISTS:
UPDATE JOB
SET JOB_TITLE = 'Head of Technology'
OFFICE = 'T06',
START_DATE = '26-JUN-17, 08:00'
WHERE START_DATE = '05-FEB-09, 08:00' AND
OFFICE = 'T06' AND
EXISTS (SELECT 1
FROM STAFF
WHERE JOB.STAFF_ID = STAFF.STAFF_ID AND
STAFF.LAST_NAME = 'Parker'
);
answered Jan 1 at 14:14
Gordon LinoffGordon Linoff
784k35310415
add a comment |
I would use EXISTS:
UPDATE JOB
SET JOB_TITLE = 'Head of Technology'
OFFICE = 'T06',
START_DATE = '26-JUN-17, 08:00'
WHERE START_DATE = '05-FEB-09, 08:00' AND
OFFICE = 'T06' AND
EXISTS (SELECT 1
FROM STAFF
WHERE JOB.STAFF_ID = STAFF.STAFF_ID AND
STAFF.LAST_NAME = 'Parker'
);
answered Jan 1 at 14:14
Gordon LinoffGordon Linoff
784k35310415
add a comment |
I would use EXISTS:
UPDATE JOB
SET JOB_TITLE = 'Head of Technology'
OFFICE = 'T06',
START_DATE = '26-JUN-17, 08:00'
WHERE START_DATE = '05-FEB-09, 08:00' AND
OFFICE = 'T06' AND
EXISTS (SELECT 1
FROM STAFF
WHERE JOB.STAFF_ID = STAFF.STAFF_ID AND
STAFF.LAST_NAME = 'Parker'
);
answered Jan 1 at 14:14
Gordon LinoffGordon Linoff
784k35310415
I would use EXISTS:
UPDATE JOB
SET JOB_TITLE = 'Head of Technology'
OFFICE = 'T06',
START_DATE = '26-JUN-17, 08:00'
WHERE START_DATE = '05-FEB-09, 08:00' AND
OFFICE = 'T06' AND
EXISTS (SELECT 1
FROM STAFF
WHERE JOB.STAFF_ID = STAFF.STAFF_ID AND
STAFF.LAST_NAME = 'Parker'
);
answered Jan 1 at 14:14
Gordon LinoffGordon Linoff
784k35310415
answered Jan 1 at 14:14
Gordon LinoffGordon Linoff
784k35310415
answered Jan 1 at 14:14
Gordon LinoffGordon Linoff
784k35310415
answered Jan 1 at 14:14
Gordon LinoffGordon Linoff
784k35310415
784k35310415
add a comment |
add a comment |
The most native way is to use updatable join view
Simple make you join and select all relevant columns, than add UPDATE modifying the join.
update (
select job.*
FROM JOB
JOIN STAFF
ON JOB.STAFF_ID = STAFF.STAFF_ID
WHERE STAFF.LAST_NAME = 'Parker')
set JOB_TITLE = 'Head of Technology',
START_DATE = '26-JUN-17, 08:00'
The only requirement is that the column STAFF_ID in STAFF table is backed with a unique index (e.g. primary key).
Without this you get the ERROR ORA-01779: cannot modify a column which maps to a non key-preserved table
This way of UPDATE is very usefull if you need to update with a value from the joined table (not your case).
answered Jan 1 at 16:13
Marmite BomberMarmite Bomber
8,07231034
add a comment |
The most native way is to use updatable join view
Simple make you join and select all relevant columns, than add UPDATE modifying the join.
update (
select job.*
FROM JOB
JOIN STAFF
ON JOB.STAFF_ID = STAFF.STAFF_ID
WHERE STAFF.LAST_NAME = 'Parker')
set JOB_TITLE = 'Head of Technology',
START_DATE = '26-JUN-17, 08:00'
The only requirement is that the column STAFF_ID in STAFF table is backed with a unique index (e.g. primary key).
Without this you get the ERROR ORA-01779: cannot modify a column which maps to a non key-preserved table
This way of UPDATE is very usefull if you need to update with a value from the joined table (not your case).
answered Jan 1 at 16:13
Marmite BomberMarmite Bomber
8,07231034
add a comment |
The most native way is to use updatable join view
Simple make you join and select all relevant columns, than add UPDATE modifying the join.
update (
select job.*
FROM JOB
JOIN STAFF
ON JOB.STAFF_ID = STAFF.STAFF_ID
WHERE STAFF.LAST_NAME = 'Parker')
set JOB_TITLE = 'Head of Technology',
START_DATE = '26-JUN-17, 08:00'
The only requirement is that the column STAFF_ID in STAFF table is backed with a unique index (e.g. primary key).
Without this you get the ERROR ORA-01779: cannot modify a column which maps to a non key-preserved table
This way of UPDATE is very usefull if you need to update with a value from the joined table (not your case).
answered Jan 1 at 16:13
Marmite BomberMarmite Bomber
8,07231034
The most native way is to use updatable join view
Simple make you join and select all relevant columns, than add UPDATE modifying the join.
update (
select job.*
FROM JOB
JOIN STAFF
ON JOB.STAFF_ID = STAFF.STAFF_ID
WHERE STAFF.LAST_NAME = 'Parker')
set JOB_TITLE = 'Head of Technology',
START_DATE = '26-JUN-17, 08:00'
The only requirement is that the column STAFF_ID in STAFF table is backed with a unique index (e.g. primary key).
Without this you get the ERROR ORA-01779: cannot modify a column which maps to a non key-preserved table
This way of UPDATE is very usefull if you need to update with a value from the joined table (not your case).
answered Jan 1 at 16:13
Marmite BomberMarmite Bomber
8,07231034
answered Jan 1 at 16:13
Marmite BomberMarmite Bomber
8,07231034
answered Jan 1 at 16:13
Marmite BomberMarmite Bomber
8,07231034
answered Jan 1 at 16:13
Marmite BomberMarmite Bomber
8,07231034
8,07231034
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%2f53996087%2fupdate-table-using-join-sql-oracle%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
Please show what tables the columns are coming from.
– Gordon Linoff
Jan 1 at 14:13