ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
Solved
UPDATE SQL with FROM and JOIN Statements
Posted on 2011-03-08
Hi Experts
The SQL Below is from SQL SErver. Following that is my attempt at an Oracle equivalent - but it is not working
UPDATE AQD.qa_CheckList_Item SET Column1 = Standard_Item_GUID
FROM AQD.qa_CheckList_Item CI
INNER JOIN AQD.qa_StandardItem SI ON CI.column1 = SI.Standard_Item_ID
INNER JOIN AQD.qa_Checklist_Propertie
WHERE (CP.ColumnID = 1)
AND (ISNUMERIC(CI.Column1) = 1) -- Identities only
AND (CP.Source IS NOT NULL) -- we have a Standard
AND (SI.[Standard] = CP.Source) -- Standard must match
And in Oracle
UPDATE QA_CHECKLIST_ITEM
SET QA_CHECKLIST_ITEM.COLUMN1 = (SELECT DISTINCT CAST(sys.utl_raw.Cast_To_V
FROM AQD.QA_STANDARDITEM_SOURCE
INNER JOIN AQD.QA_CHECKLIST_ITEM CI ON CI.COLUMN1 = SI.STANDARD_ITEM_ID
INNER JOIN AQD.QA_CHECKLIST_PROPERTIE
WHERE (CP.COLUMNID = 1)
AND LENGTH(TRIM(TRANSLATE(CI.C
AND CP.SOURCE IS NOT NULL
AND SI.STANDARD = CP.SOURCE
AND rownum = 1);
The SQL Below is from SQL SErver. Following that is my attempt at an Oracle equivalent - but it is not working
UPDATE AQD.qa_CheckList_Item SET Column1 = Standard_Item_GUID
FROM AQD.qa_CheckList_Item CI
INNER JOIN AQD.qa_StandardItem SI ON CI.column1 = SI.Standard_Item_ID
INNER JOIN AQD.qa_Checklist_Propertie
WHERE (CP.ColumnID = 1)
AND (ISNUMERIC(CI.Column1) = 1) -- Identities only
AND (CP.Source IS NOT NULL) -- we have a Standard
AND (SI.[Standard] = CP.Source) -- Standard must match
And in Oracle
UPDATE QA_CHECKLIST_ITEM
SET QA_CHECKLIST_ITEM.COLUMN1 = (SELECT DISTINCT CAST(sys.utl_raw.Cast_To_V
FROM AQD.QA_STANDARDITEM_SOURCE
INNER JOIN AQD.QA_CHECKLIST_ITEM CI ON CI.COLUMN1 = SI.STANDARD_ITEM_ID
INNER JOIN AQD.QA_CHECKLIST_PROPERTIE
WHERE (CP.COLUMNID = 1)
AND LENGTH(TRIM(TRANSLATE(CI.C
AND CP.SOURCE IS NOT NULL
AND SI.STANDARD = CP.SOURCE
AND rownum = 1);
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
2
5 Comments
The oracle update statement you have posted is a statement with update value derived from a sql statement. If the sql returns a value the update should work fine.
Do you want to convert this update statement into one with where clause?
--choulssa
Do you want to convert this update statement into one with where clause?
--choulssa
One way of doing this
--choukssa
update qa_checklist_item qci
set qci.column1 =
(
select distinct cast(sys.utl_raw.cast_to_varchar2(standard_item_guid) as varchar2(38))
from aqd.qa_standarditem_source si,
aqd.qa_checklist_properties cp
where qci.column1 = si.standard_item_id
and qci.checklist_code = cp.checklist_code
and cp.columnid = 1
and length(trim(translate(qci.column1,' +.01234656789',' '))) is null
and cp.source is not null
and si.standard = cp.source
and rownum = 1
)
where exists
(
select 1
from aqd.qa_standarditem_source si,
aqd.qa_checklist_properties cp
where qci.column1 = si.standard_item_id
and qci.checklist_code = cp.checklist_code
and cp.columnid = 1
and length(trim(translate(qci.column1,' +.01234656789',' '))) is null
and cp.source is not null
and si.standard = cp.source
and rownum = 1
)
--choukssa
Active 6 days ago
Thanks fiolks for the pointers. Managed to use a conversion tool which gave me close to the following. After some editing and playing around the following works. Appreciate the help, so points shared
MERGE INTO qa_CheckList_Item CI
USING (SELECT CI.ROWID row_id, SI.STANDARD_ITEM_GUID
FROM qa_CheckList_Item CI
JOIN qa_standarditem_source SI
ON CI.column1 = SI.Standard_Item_ID
JOIN qa_Checklist_Properties CP
ON CI.CheckList_Code = CP.CheckList_Code
WHERE ( CP.ColumnID = 1 )
AND ( sqlserver_utilities.isnume
)
AND ( CP.Source IS NOT NULL
)
AND ( SI.STANDARD = CP.Source
)) src
ON ( CI.ROWID = src.row_id )
WHEN MATCHED THEN UPDATE SET Column1 = CAST(sys.utl_raw.Cast_To_V
Kelvin
MERGE INTO qa_CheckList_Item CI
USING (SELECT CI.ROWID row_id, SI.STANDARD_ITEM_GUID
FROM qa_CheckList_Item CI
JOIN qa_standarditem_source SI
ON CI.column1 = SI.Standard_Item_ID
JOIN qa_Checklist_Properties CP
ON CI.CheckList_Code = CP.CheckList_Code
WHERE ( CP.ColumnID = 1 )
AND ( sqlserver_utilities.isnume
)
AND ( CP.Source IS NOT NULL
)
AND ( SI.STANDARD = CP.Source
)) src
ON ( CI.ROWID = src.row_id )
WHEN MATCHED THEN UPDATE SET Column1 = CAST(sys.utl_raw.Cast_To_V
Kelvin
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month9 days, 12 hours left to enroll
609 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.