| update statement [message #190692] |
Thu, 31 August 2006 15:10  |
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  |
rleishman
Messages: 3728
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
