How to achieve this kind of update for a left join?

[kamiller42]

I have a Document table and a Master Document table. Document has a list of documents. Master Document stores the documents associated with an account.

This is the SQL to pull the records...

Code: Select all

select MD.MASTER_DOCUMENT_ID, MD.MASTER_ID, MD.DOCUMENT_LINK
,D.Document_Id, D.DOCUMENT_TITLE,D.INTERNAL_TITLE, D.REQUIRED_YN
from Document D 
left join Master_Document MD on MD.DOCUMENT_ID=D.Document_ID and MD.MASTER_ID=:Master_ID
where (D.Product_Cd=:Product_CD or D.Product_Cd is null)
and D.Document_Domain_Cd=:Document_Domain_Cd

In this dataset, there will never be inserts or deletes. Well, maybe deletes from Master Document. So, my focus is all on the Update SQL. This is what I have for the update SQL in the component...

Code: Select all

INSERT INTO Master_Document
  (MASTER_ID, DOCUMENT_ID)
select :Master_ID, :Document_ID
where not exists (select 1 from Master_Document 
                  where Master_ID=:Master_ID and Document_ID=:Document_ID)

if @@rowcount0
  set :Master_Document_Id=scope_identity()

UPDATE Master_Document
SET
  DOCUMENT_LINK = :DOCUMENT_LINK
WHERE
  MASTER_DOCUMENT_ID = :MASTER_DOCUMENT_ID

In the BeforeUpdateExecute, I set Master_Document_ID to inputoutput. The update always fails because return count is 0 for new records in Master Document. Updates to existing records works fine.

How can achieve what my update SQL is saying? Maybe an "if exists" test around all insert logic else update.

[kamiller42]

Add this to the failure list...

Code: Select all

if not exists(select 1 from Master_Document 
                  where Master_ID=:Master_ID and Document_ID=:Document_ID)
begin
INSERT INTO Master_Document
  (MASTER_ID, DOCUMENT_ID, DOCUMENT_LINK)
select :Master_ID, :Document_ID, :DOCUMENT_LINK

set :Master_Document_Id=scope_identity()
end
else
begin
UPDATE Master_Document
SET
  DOCUMENT_LINK = :DOCUMENT_LINK
WHERE
  MASTER_DOCUMENT_ID = :OLD_MASTER_DOCUMENT_ID
end

[kamiller42]

Anyone perform updates on datasets with left joins? AudreyZ, any ideas on this one?

[kamiller42]

I figured this out. In the AfterExecuteUpdate event, I retrieve the parameter representing the identity field and assign its value to the identity TField. Not obvious, but seems to work.

[AndreyZ]

You can use the second SQL query for updating records, and you don't have to use the AfterUpdateExecute event for assigning the identity field value. To return new values of fields to dataset after insert and update operations automatically, you should set the ReturnParams option to True. In this case, SDAC will fill the identity field value on its own.