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:
[code=oracle]
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[/code]

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]

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]

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:

[code=oracle]
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)[/code]


Thanks again,

Derek

[amitpatel66]

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
FROM T1, T2, T3
WHERE T2.2PK = T3.T2FK AND T3.T1FK = T1.1PK AND
partcode-whcode <> T1.partcode || '-' || T2.whcode)[/code]


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:

[code=oracle]

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)

[/code]

After the update, you can fetch the data from the table using concatenation method:

[code=oracle]

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

[/code]

[derekedw]

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:

[code=oracle]

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)

[/code]

After the update, you can fetch the data from the table using concatenation method:

[code=oracle]

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

[/code]

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 || '-' || 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)
[/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:

[code=oracle]

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)

[/code]

[derekedw]

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

[code=oracle]

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)

[/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:

[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 <> pa.pa_code || '-' || wh.wh_code
[/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]

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 <> pa.pa_code || '-' || wh.wh_code
[/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]

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]

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:

[code=oracle]

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)

[/code]

[derekedw]

Try this:

[code=oracle]

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)

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