CODEWITHSUNDEEP
sqloracle-database
adzmet1
adzmet1

Reputation: 53

Oracle SQL Update set

I am trying to update a column with a count (+1) based on a column in the same table matching that of another, I have tried below:

UPDATE table1 p
   SET (p.f_dup_count)= ((p.f_dup_count) + 1)
      WHERE (SELECT a.f, 
                    p.f
               FROM table2 a,
                    table1 p
              WHERE a.f = p.f
                AND a.a_id = p.a_id)

This currently results in:

Error at Command Line:28 Column:40 Error report: SQL Error: ORA-00936: missing expression 00936. 00000 -  "missing expression"
*Cause:    
*Action:

A very helpful solution was provided for this, as below:

UPDATE table1 p SET f_dup_count = (case when exists (SELECT 1 FROM table2 a WHERE a.f = p.f AND a.a_id = p.a_id ) then f_dup_count + 1 else 0 end);

However, now I have tested it I have realised the processing time is quite High. I there a way to do this as a 'create table as select' in order to improve performance?

thanks,

Adam

Upvotes: 2

Views: 336

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

I am guessing that the point is to update the dup count when the corresponding record is found in table2.

If so, you need to change the subquery to a correlated subquery, by removing the reference to table1 in it. Then, I'm guessing that you want to check for existence of a corresponding record in table2, which you can do using exists:

UPDATE table1 p
     SET f_dup_count = f_dup_count + 1
     WHERE exists (SELECT 1
                   FROM table2 a
                   WHERE a.f = p.f AND
                         a.a_id = p.a_id
                  );

EDIT:

If you want to increment or set to 1, then you can either use two updates or move the condition into a case statement:

UPDATE table1 p
     SET f_dup_count = (case when exists (SELECT 1
                                          FROM table2 a
                                          WHERE a.f = p.f AND
                                                a.a_id = p.a_id
                                         )
                             then f_dup_count + 1
                             else 0
                         end);

Upvotes: 1

Andomar
Andomar

Reputation: 238296

A where clause should contain a condition like where ID = 42. Yours has just a value, like where 42. Oracle doesn't know what to do with that value.

One way to solve your problem in Oracle is a subquery update, like:

UPDATE  (
        SELECT p.f_dup_count
        FROM   table2 a
        JOIN   table1 p
        ON     a.f = p.f
               AND a.a_id = p.a_id
        ) SubQueryAlias
SET     f_dup_count = f_dup_count + 1

Upvotes: 0

Related Questions