UPDATE from other table

[welrogie]

Hi,

I would like to update some records in table ORG from table TEMP.
All I can come up with is this:

UPDATE ORG
SET ORG.FIELD1 = (SELECT T.FIELD1
FROM ORG O, TEMP T
WHERE O.FIELD1=T.FIELD1)

But I get the message, that no unique record is returned.

How can I alter this sql-statement, so that I can update the record that is currently being accessed?

Am I being clear enough, or does this sound 'illogical captain'?

Thanx.

[julian]

Do you have a unique constraint on ORG.FIELD1?

[jmodic]

Originally posted by welrogie
UPDATE ORG
SET ORG.FIELD1 = (SELECT T.FIELD1
FROM ORG O, TEMP T
WHERE O.FIELD1=T.FIELD1)

Change that to:

UPDATE ORG O
SET O.FIELD1 = (SELECT T.FIELD1
FROM TEMP T
WHERE O.FIELD1=T.FIELD1);

This will update *all* your records in ORG - if you want to select only *some* of the records (as you say in your post) you must add some WHERE condition in the UDATE statemnet...

[welrogie]

Yes, I've got a unique constraint on ORG.FIELD1.

[jmodic]

Can you give us some sample data of ORG and TEMP and explain what you are trying to achieve?

[welrogie]

The table ORG contains the following data:
CLIENTID, NAME, CITY, DEPARTMENT

An example:
001 name1 city1 department1
002 name2 city2 department2

The table TEMP contains the same data:
CLIENTID, NAME, CITY, DEPARTMENT

An example:
001 name1 city9 department9
002 name2 city9 department9
003 name3 city3 department3
004 name4 city4 departmen4

What I'm trying to do is the following:
The TEMP table contains different information about clients than the ORG table. Whenever a client from the TEMP table already exists in the ORG table, the record should be updated in the ORG table. And if a client from the TEMP table does not exist in the ORG table, the record should be added.

In this example that means that clients 001 and 002 must be updated with city9 and department9 in the ORG table.
And clients 003 and 004 will have to be added in the ORG table.

Is this clear enough? PLEAZ HELP ME!

[welrogie]

I've added information as above. Please HELP!!!!!

[jmodic]

UPDATE org o SET o.city, o.department =
(SELECT city, department FROM temp WHERE clientid = o.clientid)
WHERE EXISTS SELECT null FROM temp WHERE clientid = o.clientid;