Why does this SQL not work !?

[OLAPer]

Hey gurus !

I am trying to run this SQL statement, but it won't run !
Please tell me why !

UPDATE 
      S_CONTACT A 
SET 
      A.CREATED_BY = (SELECT 
                            B.S_Employee_ROW_ID 
                      FROM 
                           TEMP.UP_S_CONTACT B 
                      WHERE 
                           B.S_Contact_ROW_ID = A.row_id);

Any help greatly appreciated !

 

[dbmsguy]

what error is returned?? Paul

http://www.dbmsguy.com

 

[OLAPer]

The sub select doesn't return any values, hence the SET can't assign a value as created_by is a NOT NULL.

The problem is that the sub select isn't doing the correlation, why is beyond me !

 

[OLAPer]

The problem in the SQL is that there needs to be a WHERE for the UPDATE in order to limit the update to those records that actually exist in the other table.

The S_CONTACT table contains more records than the UP_S_CONTACT table so the update needs to know which ones to update, otherwise the join results in the sub-select returning a NULL value, this can't be assigned to created_by as this field is a NOT NULL.

Regards


OLAPer