The question below comes from this site. It's an accepted answer. However, this syntax doesn't work for me. I get an error on the comma (after carts c,) that I'm missing the SET keyword. 1) Is the syntax below incorrect, and 2) if it is incorrect, then how can you update multiple rows using data from other columns, the way it's described below?
<<
the above update is incorrect :
UPDATE carts SET carts.format_code = prices.format_code
WHERE cart_item_id IN
(SELECT cart_item_id from prices, carts
WHERE blah blah blah.....)
the 'prices' table is unknown to the update statement, and only exists in the sub-select.
However, you can issue the following update commnad in order to get the requested affect :
UPDATE carts c, prices p
SET c.format_code = p.format_code
WHERE c.cart_item_id IN
(SELECT t.cart_item_id from prices r, carts t
WHERE r.<price_table_key> = p.<price_table_key>
AND blah blah blah.....)
Hope it answered your question.
>>
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
You can update multiple rows from anywhere you want in one UPDATE statement, but you can't update two tables at the same time (the accepted answer given does not make sense). Oracle is expecting the SET keyword straight after the one and only one table name. I think you will need to do something like this:
UPDATE carts c
SET c.format_code =
(SELECT DISTINCT p.format_code
FROM prices p
WHERE p.item_id = c.cart_item_id)
It is important that the SELECT statement returns <=1 row for each row in carts, otherwise you will get a TOO_MANY_ROWS exception. Therefore, c.cart_item_id MUST select a unique prices row.
Therefore I've assumed a number of things about the data model:
carts (cart_item_id, format_code, ...)
- foreign key cart_item_id references prices (item_id)
- foreign key format_code references format_codes (format_code)
prices (item_id [pk], format_code, ...)
- foreign key format_code references format_codes (format_code)
format_codes (format_code [pk])
Jeff
You are getting ORA-01779 because "(select P.cart_item as New_Cart_Item , C.cart_item as Old_Cart_Item from prices P, carts C where P.cart_item = C.cart_item and BLAH BLAH BLAH)" is not a "key-preserved" view. Oracle is very picky about what views are updateable and what views are not. It is usually much easier to update the tables separately.
If you want to update two or more tables simultaneously, an alternative is to create an explicit view on the joined tables and create INSTEAD-OF triggers to handle inserts, updates and deletes explicitly. This can be fairly complex but effective if this kind of functionality is required.
Jeff
Jeff,
The problem with that approach is that, as you said, it only allows me to update a single row at a time. I want to be able to relate the rows to each other.
What you propose only works if I want to update all rows in the table being updated to the same value in all the other tables.
Also, creating views of this data isn't really applicable. What I'm trying to do is something I do regularly in MS SQL Server. The syntax there is:
Update table
Set t.column1 = a.column1,
t.column2 = b.column2
From table t, table_a a, table_b b
Where t.table_id = a.table_id
and a.table_id = b.table_id
It is quite logical to update single column in a row with a single value... You just want to do this multiple times as specified in the where clause:
UPDATE carts c
SET c.format_code = (*single value*)
WHERE c.cart_item_id IN
(SELECT t.cart_item_id from prices r, carts t
WHERE r.<price_table_key> = p.<price_table_key>
AND blah blah blah.....)
Now the question is HOW to deliver a single value from PRICES table. It should be based on unique key between CARTS table and PRICES table. It would look like:
UPDATE carts c
SET c.format_code = (select p.format_code
from PRICES p
where C.table_key = P.table_key)
WHERE c.cart_item_id IN
(SELECT t.cart_item_id from prices r, carts t
WHERE r.<price_table_key> = p.<price_table_key>
AND blah blah blah.....)
Since I don't know the relations between the table, I can't say what is the where clause to identify unique relation between CARS and PRICES. But you know. The other thing which is bothering me is the where clause of the update statement. I think the right one is like this:
UPDATE carts c
SET c.format_code = (select p.format_code
from PRICES p
where C.<table_key> = P.<table_key>)
where EXISTS (select 'x'
from PRICES r
where R.<price_table_key> = C.<price_table_key>
and <blah blah blah>)
OR
UPDATE carts c
SET c.format_code = (select p.format_code
from PRICES p
where C.<table_key> = P.<table_key>)
where c.<price_table_key> in
(select r.<price_table_key>
from PRICES r
where <blah blah blah>)
hope this helps,
Venko
maybe i am missing something but it seems to me quite easy to do what u want updating multiple columns:
SQL Server:
Update table
Set t.column1 = a.column1,
t.column2 = b.column2
From table t, table_a a, table_b b
Where t.table_id = a.table_id
and a.table_id = b.table_id
Oracle:
UPDATE table t
SET (t.column1, t.column2) =
( SELECT a.column1, b.column2
FROM table_a a, table_b b
WHERE t.table_id = a.table_id AND
a.table_id = b.table_id
)
WHERE EXISTS
(SELECT 1 FROM table_a a, table_b b
WHERE t.table_id = a.table_id AND
a.table_id = b.table_id
)
/
Should work achieving all your goals (multiple columns update in multiple rows and corelation between tables)
jhdaniel,
"it only allows me to update a single row at a time"
You can update as many rows as you like with the one update statement. That's what the WHERE clause is for - to restrict the rows updated as per your requirements (whether to update just one row, several rows, or all rows). What I was saying is you can't update rows in more than one table with one update statement (in Oracle or SQL Server).
"What you propose only works if I want to update all rows in the table being updated to the same value in all the other tables."
UPDATE statements like "UPDATE carts c SET c.format_code = (SELECT DISTINCT p.format_code FROM prices p WHERE p.item_id = c.cart_item_id)" update individual rows in carts according to the restriction critera in the sub-select, so they are not all updated to the same value - they are individual set as you require.
venkotch and budim have given good examples of how to update multiple columns simultaneously.
Jeff
Business Accounts
Answer for Membership
by: Bigfam5Posted on 2003-05-23 at 18:22:30ID: 8575687
There are two methods you can use
UPDATE carts C SET format_code =
(select P.format_code
from prices P
where C.cart_item = P.cart_item
and BLAH BLAH BLAH )
WHERE exists
(select 1 from prices P2
where C.cart_item = P.cart_item
and BLAH BLAH BLAH )
/
OR
UPDATE (select P.cart_item as New_Cart_Item , C.cart_item as Old_Cart_Item
from prices P, carts C
where P.cart_item = C.cart_item
and BLAH BLAH BLAH )
set Old_Cart_Item = New_Cart_Item
/