ORA-00936 Missing Expression on Update 1

[prismtx]

I am trying to change a value in a parent record when the child has a specific value in a field called filename. I don't do much SQL and based this on another similar query that works. My select when run separately selects the 3 records that I need from the document table, but I get the ORA-00936 Missing Expression message, pointing to the word "in" on the 3rd line when I add the update statement. What am I missing?

update DOCUMENT
set DOCUMENTTYPE = '2'
where in
(SELECT d.DOCUMENTTYPE
FROM DOCATTACHMENT da,
DOCUMENT d
WHERE (da.DOCUMENTID =
d.DOCUMENTID) AND
da.FILENAME LIKE 'ERROR%')

 

[SantaMufasa]

Prismtx,

I cannot be absolutely certain about the expression that you wish to compare to the results of your "...IN (SELECT..." clause, but I have posted (below) at least the syntactical structure that you need to get past the syntax error:

update DOCUMENT
   set DOCUMENTTYPE = '2'
where [b]DOCUMENTTYPE[/b] in 
 (SELECT d.DOCUMENTTYPE
            FROM DOCATTACHMENT da,
                DOCUMENT d
          WHERE (da.DOCUMENTID =
                d.DOCUMENTID) AND
                da.FILENAME LIKE 'ERROR%')

Let us know if this leads to success in this case.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[prismtx]

Thanks Mufasa,
That got me past the syntax error, but now instead of updating the 3 records that I got from the select statement, it updated several thousand. What I want to do is find any DOCATTACHMENT record with a filename field that starts with ERROR. Then get the corresponding DOCUMENT record (they both have a field called DOCUMENTID which ties them together). Then in the DOCUMENT record, I want to update the type field.

If I can't get this to work, I'll list the DOCUMENTID's and update them in a separate SQL, but I was looking for a way to do it all at once.

Thanks,
prismtx

 

[SantaMufasa]

Prismtx,

You describe an UPDATE condition in your most recent post that, by no means, seems to match your UPDATE WHERE clause(s). Can you devise a WHERE clause that matches your set of desired conditions?

Alternately, if you have a working SELECT statement that displays the 3 rows that you want UPDATEd, then the simplest (and best performing) remedy is this one:

UPDATE DOCUMENT
   set DOCUMENTTYPE = '2'
 WHERE rowid in (SELECT a.rowid <-- Previously working SELECT v v v 
                   FROM document a, <table2> b
                  WHERE a.<column> = b.<column>);

Let us know if this strategy produces more satisfying results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[prismtx]

Thanks! That worked great and it is something that I can definitely reuse in the future. A star for the help!