469,282 Members | 1,701 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

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)
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:
Expand|Select|Wrap|Line Numbers
  1. UPDATE table3
  2. SET partcode-whcode = T1.partcode || '-' || T2.whcode 
  3. FROM T1, T2, T3
  4. WHERE T2.2PK = T3.T2FK AND T3.T1FK = T1.1PK AND 
  5. 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
Feb 28 '08 #1
12 26992
debasisdas
8,127 Expert 4TB
Are you facing any problem with the code ?
Feb 29 '08 #2
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
Feb 29 '08 #3
debasisdas
8,127 Expert 4TB
I can't find any reason why the code will not work in oracle.
Mar 3 '08 #4
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:

Expand|Select|Wrap|Line Numbers
  1. UPDATE table3
  2. SET partcode-whcode = (SELECT T1.partcode || '-' || T2.whcode 
  3. FROM T1, T2, T3
  4. WHERE T2.2PK = T3.T2FK AND T3.T1FK = T1.1PK AND 
  5. partcode-whcode <> T1.partcode || '-' || T2.whcode)

Thanks again,

Derek
Mar 3 '08 #5
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. UPDATE table3
  2. SET partcode-whcode = (SELECT T1.partcode || '-' || T2.whcode 
  3. FROM T1, T2, T3
  4. WHERE T2.2PK = T3.T2FK AND T3.T1FK = T1.1PK AND 
  5. 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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. UPDATE table3 T3
  3. SET (T3.partcode,T3.whcode) = (SELECT T1.partcode,T2.whcode 
  4. FROM T1, T2
  5. WHERE T2.2PK = T3.T2FK AND T1.1PK = T3.T1FK AND 
  6. T3.partcode <> T1.partcode AND
  7. T3.whcode <> T2.whcode)
  8.  
  9.  
After the update, you can fetch the data from the table using concatenation method:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT partcode||'-'||whcode from table3
  3.  
  4.  
Mar 4 '08 #6
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. UPDATE table3 T3
  3. SET (T3.partcode,T3.whcode) = (SELECT T1.partcode,T2.whcode 
  4. FROM T1, T2
  5. WHERE T2.2PK = T3.T2FK AND T1.1PK = T3.T1FK AND 
  6. T3.partcode <> T1.partcode AND
  7. T3.whcode <> T2.whcode)
  8.  
  9.  
After the update, you can fetch the data from the table using concatenation method:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT partcode||'-'||whcode from table3
  3.  
  4.  

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:

Expand|Select|Wrap|Line Numbers
  1. UPDATE f_invent_link inv
  2. SET inv.inv_record_code = (SELECT pa.pa_code || '-' || wh.wh_code 
  3. FROM f_parts pa, f_warehouse wh, f_invent_link inv2
  4. WHERE wh.wh_pk = inv2.inv_wh_fk AND pa.pa_pk = inv2.inv_pa_fk AND 
  5. pa.pa_code || '-' || wh.wh_code)
  6.  
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
Mar 4 '08 #7
amitpatel66
2,367 Expert 2GB
Alright, your small change here would be as shown in the below code. Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. UPDATE f_invent_link inv
  3. SET inv.inv_record_code = (SELECT pa.pa_code || '-' || wh.wh_code 
  4. FROM f_parts pa, f_warehouse wh
  5. WHERE wh.wh_pk = inv.inv_wh_fk AND pa.pa_pk = inv.inv_pa_fk AND 
  6. inv.inv_record_code <> pa.pa_code || '-' || wh.wh_code)
  7.  
  8.  
Mar 4 '08 #8
Alright, your small change here would be as shown in the below code. Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. UPDATE f_invent_link inv
  3. SET inv.inv_record_code = (SELECT pa.pa_code || '-' || wh.wh_code 
  4. FROM f_parts pa, f_warehouse wh
  5. WHERE wh.wh_pk = inv.inv_wh_fk AND pa.pa_pk = inv.inv_pa_fk AND 
  6. inv.inv_record_code <> pa.pa_code || '-' || wh.wh_code)
  7.  
  8.  
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:

Expand|Select|Wrap|Line Numbers
  1. UPDATE f_invent_link inv
  2. SET inv.inv_record_code = pa.pa_code || '-' || wh.wh_code
  3. FROM f_parts pa, f_warehouse wh
  4. WHERE wh.wh_pk = inv.inv_wh_fk AND pa.pa_pk = inv.inv_pa_fk AND 
  5. inv.inv_record_code <> pa.pa_code || '-' || wh.wh_code
  6.  
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!
Mar 4 '08 #9
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. UPDATE f_invent_link inv
  2. SET inv.inv_record_code = pa.pa_code || '-' || wh.wh_code
  3. FROM f_parts pa, f_warehouse wh
  4. WHERE wh.wh_pk = inv.inv_wh_fk AND pa.pa_pk = inv.inv_pa_fk AND 
  5. inv.inv_record_code <> pa.pa_code || '-' || wh.wh_code
  6.  
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?
Mar 5 '08 #10
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,
Mar 5 '08 #11
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. UPDATE f_invent_link inv
  3. SET inv.inv_record_code = (SELECT pa.pa_code || '-' || wh.wh_code 
  4. FROM f_parts pa, f_warehouse wh
  5. WHERE wh.wh_pk = inv.inv_wh_fk AND pa.pa_pk = inv.inv_pa_fk AND 
  6. inv.inv_record_code <> pa.pa_code || '-' || wh.wh_code)
  7. WHERE NOT EXISTS( SELECT 'X' FROM f_parts p, f_warehouse w WHERE inv.inv_record_code = p.pa_code || '-' || w.wh_code)
  8.  
  9.  
Mar 6 '08 #12
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. UPDATE f_invent_link inv
  3. SET inv.inv_record_code = (SELECT pa.pa_code || '-' || wh.wh_code 
  4. FROM f_parts pa, f_warehouse wh
  5. WHERE wh.wh_pk = inv.inv_wh_fk AND pa.pa_pk = inv.inv_pa_fk AND 
  6. inv.inv_record_code <> pa.pa_code || '-' || wh.wh_code)
  7. WHERE NOT EXISTS( SELECT 'X' FROM f_parts p, f_warehouse w WHERE inv.inv_record_code = p.pa_code || '-' || w.wh_code)
  8.  
  9.  
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'?
Mar 6 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by francis70 | last post: by
4 posts views Thread by susmita_ganguly | last post: by
4 posts views Thread by Don | last post: by
reply views Thread by JWM | last post: by
1 post views Thread by Saimon | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.