Solved

UPDATE SQL with FROM and JOIN Statements

Posted on 2011-03-08
5
856 Views
Last Modified: 2012-05-11
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_Properties 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_Varchar2(Standard_Item_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_PROPERTIES CP ON CI.CHECKLIST_CODE = CP.CHECKLIST_CODE
WHERE (CP.COLUMNID = 1)
  AND LENGTH(TRIM(TRANSLATE(CI.COLUMN1,' +.01234656789',' ')))IS NULL
  AND CP.SOURCE IS NOT NULL
  AND SI.STANDARD = CP.SOURCE
  AND rownum = 1);
0
Comment
Question by:Kelvin Sparks
  • 2
  • 2
5 Comments
 
LVL 2

Expert Comment

by:choukssa
ID: 35076628
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
0
 
LVL 2

Assisted Solution

by:choukssa
choukssa earned 250 total points
ID: 35076816
One way of doing this

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
)

Open in new window



--choukssa
0
 
LVL 4

Accepted Solution

by:
pinkuray earned 250 total points
ID: 35077412
there are few tools in market which will do the query conversion like SWISS SQL.

0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 35088410
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.isnumeric(CI.Column1) = 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_Varchar2(STANDARD_ITEM_GUID) AS Varchar2(38));

Kelvin
0
 
LVL 22

Author Closing Comment

by:Kelvin Sparks
ID: 35088421
Appreciate the help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now