I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
The query is:
UPDATE table1
INNER JOIN table2 ON table1.value = table2.DESC
SET table1.value = table2.CODE
WHERE table1.UPDATETYPE='blah';
That syntax isn't valid in Oracle. You can do this:
UPDATE table1 SET table1.value = (SELECT table2.CODE
FROM table2
WHERE table1.value = table2.DESC)
WHERE table1.UPDATETYPE='blah'
AND EXISTS (SELECT table2.CODE
FROM table2
WHERE table1.value = table2.DESC);
Or you might be able to do this:
UPDATE
(SELECT table1.value as OLD, table2.CODE as NEW
FROM table1
INNER JOIN table2
ON table1.value = table2.DESC
WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW
It depends if the inline view is considered updateable by Oracle ( To be updatable for the second statement depends on some rules listed here ).
set value = code –
Pomfrey table1.value = 'Single Value'. In this case EXISTS becomes any of instead for each, hence updates things which should not be updated. I used second instead. Both queries work in 11.2. –
Latishalatitude table2 –
Colicroot Use this:
MERGE
INTO table1 trg
USING (
SELECT t1.rowid AS rid, t2.code
FROM table1 t1
JOIN table2 t2
ON table1.value = table2.DESC
WHERE table1.UPDATETYPE='blah'
) src
ON (trg.rowid = src.rid)
WHEN MATCHED THEN UPDATE
SET trg.value = code;
merge into table 1 t and so forth. –
Openeyed ON, trg is the alias for the master table, table1 ("outer" table by your logic), and src references the USING group ("inner table" by your logic). But yeah, probably could've been referenced better, but I was able to follow it. –
Yordan WHEN NOT MATCHED THEN clause to be present –
Grainger UPDATE statement, you just have to use the correct syntax. I edited your Answer to remove the false statement to the contrary. –
Magnetomotive MERGE with WHERE clause:
MERGE into table1
USING table2
ON (table1.id = table2.id)
WHEN MATCHED THEN UPDATE SET table1.startdate = table2.start_date
WHERE table1.startdate > table2.start_date;
You need the WHERE clause because columns referenced in the ON clause cannot be updated.
Do not use some of the answers above.
Some suggest the use of nested SELECT, don't do that, it is excruciatingly slow. If you have lots of records to update, use join, so something like:
update (select bonus
from employee_bonus b
inner join employees e on b.employee_id = e.employee_id
where e.bonus_eligible = 'N') t
set t.bonus = 0;
See this link for more details. http://geekswithblogs.net/WillSmith/archive/2008/06/18/oracle-update-with-join-again.aspx.
Also, ensure that there are primary keys on all the tables you are joining.
UPDATE ( SELECT t1.value, t2.CODE
FROM table1 t1
INNER JOIN table2 t2 ON t1.Value = t2.DESC
WHERE t1.UPDATETYPE='blah')
SET t1.Value= t2.CODE
As indicated here, the general syntax for the first solution proposed by Tony Andrews is :
update some_table s
set (s.col1, s.col2) = (select x.col1, x.col2
from other_table x
where x.key_value = s.key_value
)
where exists (select 1
from other_table x
where x.key_value = s.key_value
)
I think this is interesting especially if you want update more than one field.
It works fine oracle
merge into table1 t1
using (select * from table2) t2
on (t1.empid = t2.empid)
when matched then update set t1.salary = t2.salary
t1.First_Name = t2.FirstName, t1.Last_Name = t2.LastName on a table after matching it on the "UserName" column (t1.UserName = t2.UserName) to retrieve their name from a table called UserInfo (select * from UserInfo) t2). The database was such where it was using UserName as a primary key to UserInfo everywhere, instead of placing FirstName and LastName in the table, directly. This fixed that! –
Yordan This following syntax works for me.
UPDATE
(SELECT A.utl_id,
b.utl1_id
FROM trb_pi_joint A
JOIN trb_tpr B
ON A.tp_id=B.tp_id Where A.pij_type=2 and a.utl_id is null
)
SET utl_id=utl1_id;
SET was doing a REPLACE and I was trying to blank a particular string in the column - turns out Oracle treats '' as null, and this field could not be nulled. I thought the syntax was merely updating a temp table instead of the real one, but I was wrong. –
Yordan Using description instead of desc for table2,
update
table1
set
value = (select code from table2 where description = table1.value)
where
exists (select 1 from table2 where description = table1.value)
and
table1.updatetype = 'blah'
;
UPDATE table1 t1
SET t1.value =
(select t2.CODE from table2 t2
where t1.value = t2.DESC)
WHERE t1.UPDATETYPE='blah';
UPDATE (SELECT T.FIELD A, S.FIELD B
FROM TABLE_T T INNER JOIN TABLE_S S
ON T.ID = S.ID)
SET B = A;
A and B are alias fields, you do not need to point the table.
Just as a matter of completeness, and because we're talking Oracle, this could do it as well:
declare
begin
for sel in (
select table2.code, table2.desc
from table1
join table2 on table1.value = table2.desc
where table1.updatetype = 'blah'
) loop
update table1
set table1.value = sel.code
where table1.updatetype = 'blah' and table1.value = sel.desc;
end loop;
end;
/
UPDATE IP_ADMISSION_REQUEST ip1
SET IP1.WRIST_BAND_PRINT_STATUS=0
WHERE IP1.IP_ADM_REQ_ID =
(SELECT IP.IP_ADM_REQ_ID
FROM IP_ADMISSION_REQUEST ip
INNER JOIN VISIT v
ON ip.ip_visit_id=v.visit_id
AND v.pat_id =3702
); `enter code here`
Oracle base has a good run down on this.
https://oracle-base.com/articles/misc/updates-based-on-queries
From this link - I used a modification of the above query which did not work for me (the answer from mathguy which uses rowid)
MERGE /*+ APPEND PARALLEL(8) */ INTO dest_table tt
USING source_table st
ON (tt.identifier = st.identifier)
WHEN MATCHED THEN
UPDATE SET tt.number = st.number;
Here I have two tables: source and dest. They both have a varchar field in common and I am adding the source identify field (PK) into the dest table.
Oracle Database 23c has added support for direct joins in update and delete:
select employee_id, salary from hr.employees
where job_id = 'ST_MAN';
EMPLOYEE_ID SALARY
----------- ----------
120 8000
121 8200
122 7900
123 6500
124 5800
update hr.employees e
set salary = max_salary
from hr.jobs j
where j.job_id = e.job_id;
select employee_id, salary from hr.employees
where job_id = 'ST_MAN';
EMPLOYEE_ID SALARY
----------- ----------
120 8500
121 8500
122 8500
123 8500
124 8500
update table1 a
set a.col1='Y'
where exists(select 1
from table2 b
where a.col1=b.col1
and a.col2=b.col2
)
© 2022 - 2024 — McMap. All rights reserved.