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)
Table 3 hold unique information joining table 1 and table 2, so it looks like this:
T3.partcode-whcode with foreign keys indicating the record relationship to T1 and T2. Here's what I have: -
UPDATE table3
-
SET partcode-whcode = T1.partcode || '-' || T2.whcode
-
FROM T1, T2, T3
-
WHERE T2.2PK = T3.T2FK AND T3.T1FK = T1.1PK AND
-
partcode-whcode <> T1.partcode || '-' || T2.whcode
On SQL Server this would work (with diff CONC method) because the FROM clause is acceptable, but Oracle doesn't use it and I'm sure there's a better way anyways, but I am just out of ideas and searching has not come up positive.
There's probably also a better way to do my join as well instead of in the WHERE clause. Any help is appreciated. If any other information is required, just ask! I am relatively new (untrained) in the SQL world, but I learn quick and retain knowledge well!!
-Derek
12 28594
Are you facing any problem with the code ?
Are you facing any problem with the code ?
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
I can't find any reason why the code will not work in oracle.
I can't find any reason why the code will not work in oracle.
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: -
UPDATE table3
-
SET partcode-whcode = (SELECT T1.partcode || '-' || T2.whcode
-
FROM T1, T2, T3
-
WHERE T2.2PK = T3.T2FK AND T3.T1FK = T1.1PK AND
-
partcode-whcode <> T1.partcode || '-' || T2.whcode)
Thanks again,
Derek
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: -
UPDATE table3
-
SET partcode-whcode = (SELECT T1.partcode || '-' || T2.whcode
-
FROM T1, T2, T3
-
WHERE T2.2PK = T3.T2FK AND T3.T1FK = T1.1PK AND
-
partcode-whcode <> T1.partcode || '-' || T2.whcode)
Thanks again,
Derek
Is the table3 and T3 the same in your update query??
And are you trying to update two different columns, then why concatnating them while update.
Try this: -
-
UPDATE table3 T3
-
SET (T3.partcode,T3.whcode) = (SELECT T1.partcode,T2.whcode
-
FROM T1, T2
-
WHERE T2.2PK = T3.T2FK AND T1.1PK = T3.T1FK AND
-
T3.partcode <> T1.partcode AND
-
T3.whcode <> T2.whcode)
-
-
After the update, you can fetch the data from the table using concatenation method: -
-
SELECT partcode||'-'||whcode from table3
-
-
Is the table3 and T3 the same in your update query??
And are you trying to update two different columns, then why concatnating them while update.
Try this: -
-
UPDATE table3 T3
-
SET (T3.partcode,T3.whcode) = (SELECT T1.partcode,T2.whcode
-
FROM T1, T2
-
WHERE T2.2PK = T3.T2FK AND T1.1PK = T3.T1FK AND
-
T3.partcode <> T1.partcode AND
-
T3.whcode <> T2.whcode)
-
-
After the update, you can fetch the data from the table using concatenation method: -
-
SELECT partcode||'-'||whcode from table3
-
-
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: -
UPDATE f_invent_link inv
-
SET inv.inv_record_code = (SELECT pa.pa_code || '-' || wh.wh_code
-
FROM f_parts pa, f_warehouse wh, f_invent_link inv2
-
WHERE wh.wh_pk = inv2.inv_wh_fk AND pa.pa_pk = inv2.inv_pa_fk AND
-
pa.pa_code || '-' || wh.wh_code)
-
This statement doesn't work, gets an error about multiple records in subquery. So there are 3 tables, f_parts, f_warehouse, and f_invent_link. The 3rd table holds transactional inventory quantities unique to part and warehouse. the field inv_record_code is supposed to hold the part code from f_parts and the warehouse code from f_warehouse joined with a dash. I have some that do not and trying to get those populated.
I think that by using 2 different aliases for f_invent_link, I can establish a relationship between the two queries (main and sub) by using a WHERE EXISTS statement, but not sure.
Hope I'm not too confusing here :)
Thanks again
derek
Alright, your small change here would be as shown in the below code. Try this: -
-
UPDATE f_invent_link inv
-
SET inv.inv_record_code = (SELECT 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 <> pa.pa_code || '-' || wh.wh_code)
-
-
Alright, your small change here would be as shown in the below code. Try this: -
-
UPDATE f_invent_link inv
-
SET inv.inv_record_code = (SELECT 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 <> pa.pa_code || '-' || wh.wh_code)
-
-
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: -
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 <> pa.pa_code || '-' || wh.wh_code
-
Oracle does not accept FROM arguments in Update statements and does not accept multiple tables in the Update argument. I know this is a tricky one!
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: -
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 <> pa.pa_code || '-' || wh.wh_code
-
Oracle does not accept FROM arguments in Update statements and does not accept multiple tables in the Update argument. I know this is a tricky one!
Since the error says it returns more than one row, now you need to decide what value you want to update with from the multiple records, the MAX value or MIN or any other value?
Since the error says it returns more than one row, now you need to decide what value you want to update with from the multiple records, the MAX value or MIN or any other value?
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 only a single PACODE-WHCODE possibility for the record set return. I think it just needs that relationship established between the main and sub queries.
Thanks again,
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 only a single PACODE-WHCODE possibility for the record set return. I think it just needs that relationship established between the main and sub queries.
Thanks again,
Try this: -
-
UPDATE f_invent_link inv
-
SET inv.inv_record_code = (SELECT 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 <> pa.pa_code || '-' || wh.wh_code)
-
WHERE NOT EXISTS( SELECT 'X' FROM f_parts p, f_warehouse w WHERE inv.inv_record_code = p.pa_code || '-' || w.wh_code)
-
-
Try this: -
-
UPDATE f_invent_link inv
-
SET inv.inv_record_code = (SELECT 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 <> pa.pa_code || '-' || wh.wh_code)
-
WHERE NOT EXISTS( SELECT 'X' FROM f_parts p, f_warehouse w WHERE inv.inv_record_code = p.pa_code || '-' || w.wh_code)
-
-
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'?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: francis70 |
last post by:
Hi,
I have these 2 problem? Is there a way in Oracle to read UNCOMMITED
data. i.e. in Oracle the normal behaviour is that a user's updates to a
table are visible to other users ONLY when the...
|
by: susmita_ganguly |
last post by:
Hi
I am trying to upgrade from oracle 8i to oracle 9i on the same server
..I don't know much abt migration .
Can anyone help me out.
Thanks.
Susmita
|
by: Don |
last post by:
Hi,
I am moving from Sybase to Oracle and I used to be able to do update
statement like this in Sybase:
UPDATE TABLE1
SET T1.field1 = T2.field2
FROM TABLE1 T1, TABLE2 T2
WHERE T1.field2...
|
by: JWM |
last post by:
I am trying to implement Oracle connection pooling for the following code,
which was written by someone else. Here is my main question -- this java
file creates code that is executed every hour,...
|
by: Saimon |
last post by:
Questo Statement in SQL Server funziona.
In Oracle PL/SQL se lo lancio funziona
Quando lo devo far funzionare da Vb.net mi si pianta e non va avanti . Sta
lì a pensare.
Come mai ?
UPDATE...
|
by: clinttoris |
last post by:
Could some please finish this line of code. For the life of me
whatever I try it does not work.
I am trying to insert the current date time into oracle. Now I know
that in oracle if I run
...
|
by: gshawn3 |
last post by:
Hi,
I am having a hard time creating a Trigger to update an Oracle
database. I am using a SQL Server 2005 Express database on a Win XP Pro
SP2 desktop, linked to an Oracle 10g database on a...
|
by: Vinod Sadanandan |
last post by:
All,
Below listed are the new features in Oracle 11g ,please join me in this discussion to generate a testcase and analyze each of the listed features .
Precompilers:...
|
by: Anonymous |
last post by:
I resolved this issue.
If you are using an Oracle XA DataSource, Oracle's JDBC driver
will automatically issue a COMMIT when it sees a "DROP TABLE foobar"
statement.
Oracle's JDBC driver...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |