Update Script Failing to Deliver

[SeaninSeattle]

I wrote the following script to update a field on a table. That table is related to two others by customer number and document number. When I run this, I get a completion notice, but no rows (and I know it's supposed get one row I prepared for it). Anyone see where I've screwed up?

Thanks,
//sse

UPDATE SOPShipmentNotificationHeader
SET SOPShipmentNotificationHeader.Email = SY01200.INET1
FROM SY01200, SOP10100
WHERE SOPShipmentNotificationHeader.CustomerID = SOP10100.CUSTNMBR
AND SOPShipmentNotificationHeader.CustomerID = SY01200.Master_ID
AND SOPShipmentNotificationHeader.DocumentNumber = SOP10100.SOPNUMBE
AND SOP10100.PRSTADCD = SY01200.ADRSCODE

Sean Engle
Admin/DirIS

 

[Dynapen]

You forgot a part of the statement. It looks like you are running your update so that it becomes the value of the select statement, but I can't tell from this which part is the select where clause and which is hte update where clause.

Example:

Update TableA
Set TableA.ID =
(Select ID from Table B where
TableB.Name = TableA.Name)
Where TableA.ID is null;


This would find all the records in TableA where the ID field is null and update them to the value of TableID assuming that it found a record where B.Name and A.Name match.

You didn't tell the statement how to get the value to put in there. Why it is giving you a completion notice I don't know, but the missing select ( and parenthesis) is what is causing your error.

 

[SeaninSeattle]

Yeah, that's what it was. Thanks for your help!

//sse Sean Engle
Admin/DirIS
ssengle@bswusa.com