Oracle Update Statement using Concatenate

derekedw

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

 
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

debasisdas

Are you facing any problem with the code ?

derekedw

Quote:

Originally Posted by debasisdas

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

debasisdas

I can't find any reason why the code will not work in oracle.

derekedw

Quote:

Originally Posted by debasisdas

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

 
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

amitpatel66

Quote:

Originally Posted by derekedw

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

 
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:

Expand|Select|Wrap|Line Numbers

  
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:

Expand|Select|Wrap|Line Numbers

SELECT partcode||'-'||whcode from table3

derekedw

Quote:

Originally Posted by amitpatel66

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

  
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:

Expand|Select|Wrap|Line Numbers

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:

Expand|Select|Wrap|Line Numbers

 
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

amitpatel66

Alright, your small change here would be as shown in the below code. Try this:

Expand|Select|Wrap|Line Numbers

  
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)

derekedw

Quote:

Originally Posted by amitpatel66

Alright, your small change here would be as shown in the below code. Try this:

Expand|Select|Wrap|Line Numbers

  
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:

Expand|Select|Wrap|Line Numbers

 
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!

amitpatel66

Quote:

Originally Posted by derekedw

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

 
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?

derekedw

Quote:

Originally Posted by amitpatel66

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,

amitpatel66

Quote:

Originally Posted by derekedw

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

  
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)

derekedw

Quote:

Originally Posted by amitpatel66

Try this:

Expand|Select|Wrap|Line Numbers

  
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'?