Updating multiple columns of a table

Question

I've two tables with the following fields:

table1 : OTNAME table2 : SNCODE, description_text

I'm trying to add the two columns of table2 to table1 and update the columns. My query is:

alter table  table1 add sncode integer                              
alter table  table1 add description_text varchar2(30)

update table1 set 
sncode,description_text = (SELECT  sncode, description_text
   FROM   table2, table1
  WHERE   SUBSTR (otname, INSTR (otname,'.', 1, 3)
                         + 1, 
                         INSTR (otname, '.', 1, 4)
                              - INSTR (otname,'.', 1, 3)
                              - 1)
                               = sncode)

I get an error: ORA 00927 - Missing Equal to Operator, pointing to the second line of my update statement. Appreciate if someone can point me in the right direction.

Regards,

novice

Quassnoi · Accepted Answer · 2009-08-05 11:11:17Z

5

MERGE
INTO    table1 t1
USING   table2 t2
ON      (SUBSTR (otname, INSTR (otname,'.', 1, 3)
                         + 1, 
                         INSTR (otname, '.', 1, 4)
                              - INSTR (otname,'.', 1, 3)
                              - 1)
                               = t2.sncode))
WHEN MATCHED THEN
UPDATE
SET    t1.sncode = t2.sncode,
       t1.description_text = t2.description_text

You also can simplify your expression:

MERGE
INTO    table1 t1
USING   table2 t2
ON      (REGEXP_SUBSTR(otname, '[^.]+', 1, 4) = t2.sncode)
WHEN MATCHED THEN
UPDATE
SET    t1.sncode = t2.sncode,
       t1.description_text = t2.description_text

edited Aug 5, 2009 at 11:11

answered Aug 5, 2009 at 10:22

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

novice Over a year ago

Thanks.. but I get a MISSING ON KEYWORD error. Is your code targetted to oracle 10g ?

Quassnoi Over a year ago

Could you please post your table definitions so that I could check the syntax?

novice Over a year ago

Hi, Not sure what exactly you meant by definitions. I'm posting the tables data types for your reference. Table 2: sncode integer description_text varchar2(30) Table 1: otname varchar2(100)

Quassnoi Over a year ago

@novice: sorry, I didn't notice them first. I updated the post, try now.

novice Over a year ago

Fantastic.. Thanks a ton ! Works fabulously!

|

nagul · Accepted Answer · 2009-08-05 10:29:57Z

1

Your problem is you are missing brackets around the fields to be updated. Try

update table1 set 
( sncode,description_text) = (SELECT  sncode, description_text
   FROM   table2, table1
  WHERE   SUBSTR (otname, INSTR (otname,'.', 1, 3)
                         + 1, 
                         INSTR (otname, '.', 1, 4)
                              - INSTR (otname,'.', 1, 3)
                              - 1)
                               = sncode)

answered Aug 5, 2009 at 10:29

nagul

2,2633 gold badges19 silver badges22 bronze badges

2 Comments

novice Over a year ago

Hi Nagul, the error I see is : ORA-01427: Single-row subquery returns more than one row Any other suggestions ?

nagul Over a year ago

My apologies. I looked only at the syntax error in your query. What this query is trying to do is update sncode, description_text for all rows with 1 set of value returned from the select query. To update each with the constructed value from existing columns, use the update..set..from or merge into..using..on syntax suggested by others.

Ray · Accepted Answer · 2009-08-05 10:22:52Z

0

Try UPDATE SET FROM construct instead. Something like

update table1 
set  sncode = t1.sncode, description_text = t1.description_text 
from table2 as t2, table1 as t1
where SUBSTR (otname, INSTR (otname,'.', 1, 3)
                     + 1, 
                     INSTR (otname, '.', 1, 4)
                          - INSTR (otname,'.', 1, 3)
                          - 1)
                           = sncode)

edited Aug 5, 2009 at 10:22

answered Aug 5, 2009 at 10:17

Ray

1,5759 silver badges10 bronze badges

4 Comments

nagul Over a year ago

No, it is about Oracle. It's not explicitly mentioned in the question, but it is tagged with oracle and oracle10g.

novice Over a year ago

I'm using Oracle 10g. I get an error saying : " SQL COMMAND NOT PROPERLY ENDED "

nagul Over a year ago

You seem to have an extra paranthesis after sncode? Run the command as select t1.sncode, t1.description_text from table2.. to first understand what's wrong. Further break down the where clause and you'll eventually get to the bit that's wrong. i.e Try select substr(...) from table2 and so on ..

Quassnoi Over a year ago

Oracle does not support UPDATE FROM

Adam Paynter · Accepted Answer · 2009-08-05 11:15:46Z

0

I suspect you should not be including table1 in the SELECT query. Perhaps this statement will work:

UPDATE table1
   SET
       (sncode, description_text)
       =
       (
         SELECT table2.sncode, table2.description_text
           FROM table2
          WHERE SUBSTR(
                  table1.otname,
                  INSTR(table1.otname,'.', 1, 3) + 1, 
                  INSTR(table1.otname, '.', 1, 4) - INSTR (table1.otname,'.', 1, 3) - 1
                ) = table2.sncode
       )

answered Aug 5, 2009 at 11:15

Adam Paynter

47.1k33 gold badges153 silver badges168 bronze badges

Collectives™ on Stack Overflow

Updating multiple columns of a table

4 Answers 4

9 Comments

2 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related