Using a Complex Query Within an UPDATE


  • I have the following code, which returns the values I expect:

    SELECT qry_MARA_DWNLD.Material, Case
                        when qry_MARA_DWNLD.Document like '%$' then replace(qry_MARA_DWNLD.document,'$','')
                        when qry_MARA_DWNLD.document like '%   L' then replace(qry_MARA_DWNLD.document,'   L','')
                        else qry_MARA_DWNLD.document
                    End as Document,
                        when qry_MARA_DWNLD.Document like '%$' then left(replace(qry_MARA_DWNLD.document,'$',''),2)
                        when qry_MARA_DWNLD.document like '%   L' then left(replace(qry_MARA_DWNLD.document,'   L',''),2)
                        else left(qry_MARA_DWNLD.document,2)
                    End as REV,
                        when qry_MARA_DWNLD.Document like '%$' then Right(replace(qry_MARA_DWNLD.document,'$',''),5)
                        when qry_MARA_DWNLD.document like '%   L' then Right(replace(qry_MARA_DWNLD.document,'   L',''),5)
                        else Right(qry_MARA_DWNLD.document,5)
                    End as ECO

    FROM QualityDB.dbo.tbl_MARA_DWNLD as qry_MARA_DWNLD

    on QualityDB.dbo.tbl_MARA_DWNLD.material=qry_MARA_DWNLD.Material
    where qry_MARA_DWNLD.Document is not null and qry_MARA_DWNLD.Document <>''

    My end goal, though, is to update the REV and ECO fields in tbl_MARA_DWNLD with the REV and ECO numbers I'm generating in the query above. I know I need to use the UPDATE command, but I have not been able to pump the values from this query into the appropriate fields in the table.

    Friday, January 31, 2014 11:42 PM


  • Seems to be a simple UPDATE:

    UPDATE	QualityDB.dbo.tbl_MARA_DWNLD 
    SET		REV = CASE	WHEN Document like '%$' THEN LEFT(REPLACE(Document,'$',''),2)
    					WHEN Document like '%   L' THEN LEFT(REPLACE(Document,'   L',''),2)
    					ELSE LEFT(Document,2)
    		ECO = CASE	WHEN Document like '%$' THEN Right(REPLACE(Document,'$',''),5)
    					WHEN Document like '%   L' THEN Right(REPLACE(Document,'   L',''),5)
    					ELSE Right(Document,5)
    WHERE	NOT Document IS NULL 
    	AND Document <> '';

    Saturday, February 01, 2014 12:31 PM