Home » SQL & PL/SQL » SQL & PL/SQL » update statement
update statement [message #190692] Thu, 31 August 2006 15:10 Go to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hello you all,

I have two tables and i need to update one table with fields of the other table.

I have this update statement




UPDATE table_a a inner join table_b b   
	   ON (a.ID = b.ID)
	   AND (a.CODE = b.CODE) 
SET a.sellDate = b.sellDate
AND a.price    = b.price
AND a.sold     = b.sold
WHERE b.indicator = 'J'





So, I need to update more than 1 column.
Can anyone tell me why this doesn't work or give another solution?

Thanks you all!...

[Updated on: Thu, 31 August 2006 19:14]

Report message to a moderator

Re: update statement [message #190713 is a reply to message #190692] Thu, 31 August 2006 23:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Updating a Join View is different in Oracle to SQL Server.
UPDATE (
   SELECT a.sellDate AS old_selldate
   ,      b.sellDate AS new_selldate
   ,      a.price    AS old_price
   ,      b.price    AS new_price
   ,      a.sold     AS old_sold
   ,      b.sold     AS new_sold
   FROM table_a a 
   INNER JOIN table_b b   
   ON a.ID = b.ID
   AND a.CODE = b.CODE
   WHERE b.indicator = 'J'
)
SET old_sellDate = new_sellDate
AND old_price    = new_price
AND old_sold     = new_sold


Note that for an Updateable Join View to work, you need a Unique or PK constraint to exist for table_b(id, code), otherwise you will get a Non-Key-Preserved error.

The more common, old-fashioned, and usually less efficient method is:
UPDATE table_a
SET (sellDate, price, sold) = (
    SELECT sellDate, price, sold
    FROM table_b
    WHERE a.ID = b.ID
    AND a.CODE = b.CODE
)
WHERE (id, code) IN (
    SELECT sellDate, price, sold
    FROM table_b
    WHERE b.indicator = 'J'
)


Ross Leishman
Re: update statement [message #190716 is a reply to message #190713] Thu, 31 August 2006 23:30 Go to previous message
Jolanda
Messages: 68
Registered: September 2004
Member
Thanks RLeishman,

I am gonna test if it works for me!

cheers
Previous Topic: how to load csv file that is in a CLOB column into a table ?
Next Topic: Set Numeric Precision when using MViews.
Goto Forum:
  


Current Time: Thu Dec 08 04:13:56 CST 2016

Total time taken to generate the page: 0.12993 seconds