Re: Update statement, then insert what wasn't available to be updated.

rhaazy (rhaazy@xxxxxxxxx) writes:
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID,
ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @MAC, b.ID, b.ParentID,
tblScanAttribute.ScanAttributeID,
@scanid, b.scanattribute, DateCreated = getdate(), LastModified =
getdate()
FROM tblScanDetail LEFT OUTER JOIN #temp a ON
(tblScanDetail.GUIID =
a.ID AND tblScanDetail.GUIParentID = a.ParentID AND
tblScanDetail.AttributeValue = a.scanattribute), tblScanAttribute JOIN
#temp b ON tblScanAttribute.Name = b.Name
WHERE (tblScanDetail.GUIID IS NULL AND
tblScanDetail.GUIParentID IS
NULL AND tblScanDetail.AttributeValue IS NULL)
...
------------------------------------------------------------
My problem is that Insert statement that follows the update into
tblScanDetail, for some reason it just seems to insert everything twice
if the update is performed. Not sure what I did wrong but any help
would be appreciated. Thanks in advance.

Since you did not seem to post the complete XML document it was a
difficult to test. And while you did post the table schemas, you did
not explain the tables, and there were no keys. And you did not include
the definition of tblScanAttibute.

But just like last night I notice that your query includes a cross
join with tblScanAttribute. Your reply was that I hit the nail on
the head, so I'm a little puzzled why you post a similar query tonight...

What also appears funny is that judging from your talk about UPDATE
and INSERT, I would expect an INSERT ... SELECT .. FROM #temp WHERE
NOT EXISTS, but your query is completely different.

Anyway, a complete sample document, the definition of tblScanAttribute
and INSERT statemetns to that table, and finally the expected result.
I think you can skip the UPDATE - at least if you get the problems
with an empty table as well.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.