Kelvin Sparks
asked on
UPDATE SQL with FROM and JOIN Statements
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 s CP ON CI.CheckList_Code = CP.CheckList_Code
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 archar2(St andard_Ite m_GUID) AS Varchar2(38))
FROM AQD.QA_STANDARDITEM_SOURCE SI
INNER JOIN AQD.QA_CHECKLIST_ITEM CI ON CI.COLUMN1 = SI.STANDARD_ITEM_ID
INNER JOIN AQD.QA_CHECKLIST_PROPERTIE S CP ON CI.CHECKLIST_CODE = CP.CHECKLIST_CODE
WHERE (CP.COLUMNID = 1)
AND LENGTH(TRIM(TRANSLATE(CI.C OLUMN1,' +.01234656789',' ')))IS NULL
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);
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ric(CI.Col umn1) = 1
)
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 archar2(ST ANDARD_ITE M_GUID) AS Varchar2(38));
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
ASKER
Appreciate the help
Do you want to convert this update statement into one with where clause?
--choulssa