Query help 1

[JasonMcConnell]

Hi Guys

I new to SQL plus and was wondering if anybody can help with the following query?

The following query returns the information needed

SELECT
SEARCH."SEARCH_NUMBER", SEARCH_TYPE."PLANNING_HISTORY"
FROM
"SEARCH" SEARCH,
"SEARCH_TYPE" SEARCH_TYPE
WHERE
SEARCH."SEARCH_TYPE" = SEARCH_TYPE."CODE" AND
SEARCH."SEARCH_NUMBER" = '2008/01439';

However I want to update a field where the application number is equal to 2008/01439. (I need to join to tables SEARCH."SEARCH_TYPE" = SEARCH_TYPE."CODE")

I have tried the following without success

UPDATE SEARCH, SEARCH_TYPE
SET = PLANNING_HISTORY = "N"
where SEARCH."SEARCH_TYPE" = SEARCH_TYPE."CODE" AND
SEARCH."SEARCH_NUMBER" = '2008/01439';

-----------

UPDATE SEARCH_TYPE, SEARCH
SET = PLANNING_HISTORY = "N"
where PLANNING_HISTORY IN
(SELECT
SEARCH."SEARCH_NUMBER",
SEARCH_TYPE."CODE", SEARCH_TYPE."PLANNING_HISTORY"
FROM
"LAND"."SEARCH" SEARCH,
"LAND"."SEARCH_TYPE" SEARCH_TYPE
WHERE
SEARCH."SEARCH_TYPE" = SEARCH_TYPE."CODE" AND
SEARCH."SEARCH_NUMBER" = '2008/01439');


ERROR at line 1:
ORA-00971: missing SET keyword


Can anybody tell me what I am doing wrong?

 

[SantaMufasa]

Jason,

I have not troubleshot your code any further than these two syntax errors:

...SET = PLANNING_HISTORY = "N"

...should instead read:

...SET PLANNING_HISTORY = 'N'

Specifically, your first "=" is a syntax error, and literals in Oracle are surrounded with single quotes (').

Also, use of double quotes (") to surround object names in Oracle is bad form. Use of double quotes to surround object names is required only when the object name was originally created with lower-case characters or characters that are otherwise illegal for use in Oracle object names...an extremely bad practice.

Let us know if this resolves your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[JasonMcConnell]

UPDATE SEARCH, SEARCH_TYPE
SET PLANNING_HISTORY = ‘N’
where SEARCH.SEARCH_TYPE = SEARCH_TYPE.CODE AND
SEARCH.SEARCH_NUMBER = '2008/01439';


ERROR at line 1:
ORA-00971: missing SET keyword


-----

UPDATE SEARCH_TYPE, SEARCH
SET PLANNING_HISTORY = ‘N’
where PLANNING_HISTORY IN
(SELECT
SEARCH.SEARCH_NUMBER,
SEARCH_TYPE.CODE, SEARCH_TYPE.PLANNING_HISTORY
FROM
LAND.SEARCH SEARCH,
LAND.SEARCH_TYPE SEARCH_TYPE
WHERE
SEARCH.SEARCH_TYPE = SEARCH_TYPE.CODE AND
SEARCH.SEARCH_NUMBER = '2008/01439');

ERROR at line 1:
ORA-00971: missing SET keyword

Mufasa,
Thanks for correcting the syntax error and for the advice on bad practice.

Unfortunately I still get the error?

 

[SantaMufasa]

Sorry that I did not look deeper at your original code...the next syntax error involves:

UPDATE SEARCH, SEARCH_TYPE...

Oracle allows you to specify only a single table at a time to update. You have specified two tables to update: SEARCH and SEARCH_TYPE.

Therefore, if you must correlate the table you are updating with contents of another table, then you do it in a correlated subquery in the WHERE statement as you have done in your latter code set, but you do not mention the second table (with which you are correlating) in the actual UPDATE-table list.

So, remove the name of the extraneous table (that you are not updating) from the UPDATE <table> clause, and try it again.

Let us know the results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[JasonMcConnell]

UPDATE SEARCH_TYPE
SET PLANNING_HISTORY = 'N'
where PLANNING_HISTORY IN
(SELECT
SEARCH.SEARCH_NUMBER, SEARCH_TYPE.PLANNING_HISTORY
FROM
LAND.SEARCH SEARCH,
LAND.SEARCH_TYPE SEARCH_TYPE
WHERE
SEARCH.SEARCH_TYPE = SEARCH_TYPE.CODE AND
SEARCH.SEARCH_NUMBER = '2008/01439');

-----

SQL> UPDATE SEARCH_TYPE
2 SET PLANNING_HISTORY = 'N'
3 where PLANNING_HISTORY IN
4 (SELECT
5 SEARCH.SEARCH_NUMBER, SEARCH_TYPE.PLANNING_HISTORY
6 FROM
7 LAND.SEARCH SEARCH,
8 LAND.SEARCH_TYPE SEARCH_TYPE
9 WHERE
10 SEARCH.SEARCH_TYPE = SEARCH_TYPE.CODE AND
11 SEARCH.SEARCH_NUMBER = '2008/01439');
(SELECT
*
ERROR at line 4:
ORA-00913: too many values


Mufasa I only need to update one table (search type) but this table needs to be joined to (search) in order to identify the search number as I only want to update one record.

I have removed the additional table that is not needed from update but now get

ERROR at line 4:
ORA-00913: too many values

Thanks for your help.

 

[jaxtell]

Try this

UPDATE SEARCH_TYPE
    SET   PLANNING_HISTORY = 'N'
    where SEARCH_NUMBER IN
    (SELECT
        SEARCH.SEARCH_NUMBER
    FROM
        LAND.SEARCH SEARCH,
        LAND.SEARCH_TYPE SEARCH_TYPE
    WHERE
       SEARCH.SEARCH_TYPE = SEARCH_TYPE.CODE AND
       SEARCH.SEARCH_NUMBER = '2008/01439');

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[JasonMcConnell]

Hi Jaxtell,

Tried that but now get ERROR at line 3:
ORA-00904: "SEARCH_NUMBER": invalid identifier

any ideas?

Thanks for your help

 

[jaxtell]

How about this?

UPDATE SEARCH_TYPE
    SET   PLANNING_HISTORY = 'N'
    where rowid IN
    (SELECT
        SEARCH_TYPE.rowid
    FROM
        LAND.SEARCH SEARCH,
        LAND.SEARCH_TYPE SEARCH_TYPE
    WHERE
       SEARCH.SEARCH_TYPE = SEARCH_TYPE.CODE AND
       SEARCH.SEARCH_NUMBER = '2008/01439');

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[SantaMufasa]

Jason,

The reason that you are receiving that error is because you are comparing a single expression ("...where PLANNING_HISTORY") to multiple (two) expressions ("...SELECT SEARCH.SEARCH_NUMBER, SEARCH_TYPE.PLANNING_HISTORY").

Here is a simplified (yet initially a little bit more difficult to understand) technique to achieve what you want:

SQL> select * from search_type;

CODE  PLANNING_HISTORY
----- ----------------
xyz
abc
def

3 rows selected.

SQL> select * from search;

SEARC SEARCH_NUMBER
----- -------------
abc   2008/01439
xyz   2008/01439
def   2008/09999

3 rows selected.

UPDATE SEARCH_TYPE x
SET   PLANNING_HISTORY = 'N'
where exists
(SELECT 'hello Jason'
   FROM SEARCH y
  WHERE y.SEARCH_TYPE = x.CODE
    AND y.SEARCH_NUMBER = '2008/01439');

2 rows updated.

select * from search_type;

CODE  PLANNING_HISTORY
----- ----------------
xyz   N
abc   N
def

3 rows selected.

Let us know if this proves useful for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[JasonMcConnell]

Thanks Guys.

 

[BJCooperIT]

Jason,

One of the nice things about Tek-Tips is the ability to award nice folks, who take the time to help you, with a little purple star. It only takes a moment to click the link that says "Thank ???? for this valuable post!" at the bottom of their post.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com