This worked!!
One question though...in the Where Not Exists argument, you're querying a specific value, just any record return for the link you want? You can SELECT 'ANYTHING'?...
derekedw
Last Activity: Mar 24 '08, 03:05 PM
Joined: Feb 28 '08
Location:
-
It returns more than one row because I am not establishing a link between the data returned in the sub query and the table of the primary update query. This is where I believe the WHERE EXISTS statement comes into play, but I am not for sure and wouldn't know how to formulate it.
Again, I could be way off on this assumption, so happy to hear any suggestions.
There's not a MAX or MIN value to to choose though. There's...Leave a comment:
-
Same problem I ran into originally: ORA-01427: single-row subquery returns more than one row. That's why I wanted to use this one that works in SQL Server and just get it working with Oracle:
[code=oracle]
UPDATE f_invent_link inv
SET inv.inv_record_ code = pa.pa_code || '-' || wh.wh_code
FROM f_parts pa, f_warehouse wh
WHERE wh.wh_pk = inv.inv_wh_fk AND pa.pa_pk = inv.inv_pa_fk AND
inv.inv_record_ code...Leave a comment:
-
Thanks for your response. I am actually trying to concatenate the two fields from table 1(part code) and table 2(warehouse code) to a single field in table 3 that separates the identifiers with a dash. the established relationship already in place are foreign keys stored in table 3. Here's the ACTUAL table names and code I am working with:
[code=oracle]
UPDATE f_invent_link inv
SET inv.inv_record_ code = (SELECT pa.pa_code...Leave a comment:
-
I get a 'SQL Statement Improperly Ended' error. I don't think oracle accepts 'FROM' in UPDATE STATEMENTS. I normally would use a subquery but get errors on that because the subquery results in multiple rows per line, so I need a way to join and represent multiple tables in the main query. My subquery would look like this:
[code=oracle]
UPDATE table3
SET partcode-whcode = (SELECT T1.partcode || '-' || T2.whcode
...Leave a comment:
-
Thanks for your reply. Yes, this statement won't work with Oracle. I am wondering how to rework the code to update the field to a concatenate for the two source fields combined with a dash.
My problem is really the table linking and how to tell Oracle my source fields from different tables than the table being updated.
Thanks again,
Derek...Leave a comment:
-
Oracle Update Statement using Concatenate
Hi guys,
This is my first post out here. I read the guidelines, did not find anything relevant via searching, so I would like some help here.
I am trying to write an update statement on a table based on fields from two other tables to a third joined table.
Table 1 has a field part code and a primary key (T1.partcode, T1.1PK)
Table 2 has a field warehouse code and a primary key (T2.whcode, T2.2PK)...
No activity results to display
Leave a comment: