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
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);
Oracle DatabaseSQL
Last Comment
Kelvin Sparks
SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER CERTIFIED SOLUTION
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
)
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
ASKER
Appreciate the help
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Do you want to convert this update statement into one with where clause?
--choulssa