update or insert??

[sammybee77]

Hi All,

Attached is the query I'm trying to do, obviously it doesn't work as I'm trying to set the value of one table whilst referencing another. Any ideas of the best way to do this?


update table1, table2 set tab1id = tab2id, tab1field1 = 8888, tab1field3 = 0
where tab2name like 'TEST%';

The other problem I have is that tab1id may already be populated with tab2id or it may be null. In theory I think I need both insert and update - any ideas?

Many thanks

Sam

 

[jee]

Maybe I am just dense, but I can't figure out what you are trying to do. Could you post the layout of each of the tables, give some sample data and an example of what you are trying to do, using the sample data?

 

[Naith]

Not really sure what the get up is here, either.

Are you trying to do something like this, Sammy?

UPDATE table1
SET table1.id =
(select table2.id from table2),
table1.field1 = 8888,
table1.field3 = 0
WHERE --table1.name IN
(SELECT table2.name
FROM table2
WHERE table2.name LIKE 'TEST%');

Naith

 

[sammybee77]

Hi Naith,

Thats the kind of thing! Cheers

Only problem is there is no table1.name, I amended the query like this

UPDATE table1
SET table1.id =
(select table2.id from table2),
table1.field1 = 8888,
table1.field3 = 0
WHERE --table1.id IN
(SELECT table2.id
FROM table2
WHERE table2.name LIKE 'TEST%');

Then came the next problem table2.id has multiple rows with same id. I tried putting a distinct in front of table2.id in the first select statement. I'm now getting this error:

ora 01427: single row subquery returns more than one row.

Any more ideas?

Many thanks

Sam

 

[Naith]

Qualify the first select with a WHERE clause that makes the returned results return a single row. Like:

FROM table2,table1
WHERE table2.id = table1.id

or something similar.

All the best,

Naith

 

[Bimalaggarwal]

if I undestand your req. correctly, you can use the following script (or something similar): (replace the query in update..where rownum..... as needed by you tp specify more precise selection criteria)
------------------------------------------


begin
update table1
set tab1id=(select tab2id from table2 where tab2name like 'TEST%' and rownum < 2) --put specific criteria as per your need
,tab1field1 = 8888
,tab1field3 = 0;

when sql%not_found then
insert into table1(tab1id, tab1field1, tab1field3 )
as select tab2id,8888,0 from table2 where tab2name like 'TEST%';

end;

 

[sammybee77]

Hi guys,

my plsql is awful! I tried the above and got an error on the when clause (expecting a declare or if loop) also on the secound part shouldn't the (as select) be values?

Tab1field1 also contains multiple rows if there are rows with different id's already there I don't want them overwritten to all read 8888! Hope this makes sense.

Many thanks

Sam