SET updates? - SOLVED - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SET updates? - SOLVED

Author

Topic

rhein
Starting Member

9 Posts

Posted - 2003-07-30 : 18:27:40

Okay, here's another question:

I have the bulk insert working (or at least it's coded and VS.Net doesn't bark about it)
INSERT INTO Parties
SELECT id, fkPartiesType_id, fName, lName, busName, address, city, state, zip, zip4, phone, taxID, req1099, comments, uiId, @sprocTimeStamp
FROM OPENXML (@iPartyDoc,'parties/party',2)
WITH( id INT,
fkPartiesType_id VARCHAR(50),
fName VARCHAR(50),
lName VARCHAR(50),
busName VARCHAR(50),
address VARCHAR(50),
city VARCHAR(50),
state VARCHAR(2),
zip VARCHAR(50),
zip4 VARCHAR(50),
phone VARCHAR(50),
taxID VARCHAR(50),
req1099 VARCHAR(50),
comments VARCHAR(50),
uiId INT
)
WHERE id = NULL

Now how would I do a similar Update proceedure? I want to update the records if the ID field is passed in in the XML.

Here's what I have so far.
UPDATE Parties
SET fkPartiesType_id = tmp.fkPartiesType_id,
fName = tmp.fName,
lName = tmp.lName,
busName = tmp.busName,
address = tmp.address,
city = tmp.city,
state = tmp.state,
zip = tmp.zip,
zip4 = tmp.zip4,
phone = tmp.phone,
taxID = tmp.taxID,
req1099 = tmp.req1099,
comments = tmp.comments,
uiId = tmp.uiId,
sprocTimeStamp = @sprocTimeStamp
(SELECT id, fkPartiesType_id, fName, lName, busName, address, city, state, zip, zip4, phone, taxID, req1099, comments, uiId, @sprocTimeStamp
FROM OPENXML (@iPartyDoc,'parties/party',2)
WITH( id INT,
fkPartiesType_id VARCHAR(50),
fName VARCHAR(50),
lName VARCHAR(50),
busName VARCHAR(50),
address VARCHAR(50),
city VARCHAR(50),
state VARCHAR(2),
zip VARCHAR(50),
zip4 VARCHAR(50),
phone VARCHAR(50),
taxID VARCHAR(50),
req1099 VARCHAR(50),
comments VARCHAR(50),
uiId INT
) AS tmp
WHERE id != NULL)
WHERE parties.id = tmp.id

But VS.NET doesn't like this. What am I doing wrong? Can I even do updates like this?

Thanks,
Rick

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-07-30 : 19:41:12

First up... VS.NET isn't a database server so it is in no place to object

But you are very close, it's just syntax..

Take a look at this : http://www.sqlteam.com/item.asp?itemID=3876

Damian

rhein
Starting Member

9 Posts

Posted - 2003-07-31 : 13:05:54

Thanks for the help, but I still don't see how it's supposed to work.

Here's what I'm thinking:
I have a table (Really it's XML) that I want to do a select from, for every row in the select that I have a matchin row in the parties table, I want to update the parties table.

I have the select from the XML file working.
Here is what the xml looks like.
<parties>
<party>
<id>3</id>
<uid>1</uid>
<fkPartiesType_id>1</fkPartiesType_id>
<fName>Rick</fName>
<lName>Hein</lName>
<mName>Jim</mName>
<busName>Mutual Of Enumclaw</busName>
<address>1460 Well St</address>
<city>Enumclaw</city>
<state>WA</state>
<zip>98022</zip>
<zip4>1234</zip4>
<phone>8003665551</phone>
<taxID></taxID>
<req1099>N</req1099>
<comments>This is a test</comments>
</party>
<party>
<uid>2</uid>
<fkPartiesType_id>1</fkPartiesType_id>
<fName>Homer</fName>
<lName>Simpson</lName>
<mName>j</mName>
<busName>Burns Nuclear</busName>
<address>1234 Glowing Drive</address>
<city>Springfield</city>
<state>IL</state>
<zip>12345</zip>
<zip4>1234</zip4>
<phone>8005551212</phone>
<taxID></taxID>
<req1099>n</req1099>
<comments>MMMMM Donuts</comments>
</party>
</parties>

Here is what the select looks like
SELECT id, fkPartiesType_id, fName, lName, busName, address, city, state, zip, zip4, phone, taxID, req1099, comments, uiId, @sprocTimeStamp
FROM OPENXML (@iPartyDoc,'parties/party',2)
WITH( id INT,
fkPartiesType_id VARCHAR(50),
fName VARCHAR(50),
lName VARCHAR(50),
busName VARCHAR(50),
address VARCHAR(50),
city VARCHAR(50),
state VARCHAR(2),
zip VARCHAR(50),
zip4 VARCHAR(50),
phone VARCHAR(50),
taxID VARCHAR(50),
req1099 VARCHAR(50),
comments VARCHAR(50),
uiId INT
)AS TMP
WHERE id IS NOT NULL

This returns those records that have an id (only one in the example above). That id matches an id field in the parties table.

The update is a recordset, not a table. This example on the link you provided looks (to me) to be the closest to what I want to do.

Update Products
SET Products.UnitPrice = Holding.UnitPrice
FROM
Holding
WHERE
Products.ProductID = Holding.ProductID

But the From Holdng is not a table in my case it's a select, AND I want to update more than one field.

Thanks for the help.
Rick

rhein
Starting Member

9 Posts

Posted - 2003-07-31 : 18:26:24

Okay, I figured out how to do it. Hopefully this will help someone else.
Here is the sample code - Including the xml.

ALTER PROCEDURE dbo.test
--(
--)

AS

/* TEST DATA */

DECLARE @partyXML VARCHAR(5000)
DECLARE @sprocTimeStamp DATETIME
DECLARE @iPartyDoc INT
SET @sprocTimeStamp = GETDATE()

SET @partyXML =
'<parties>
<party>
<id>3</id>
<uid>1</uid>
<fkPartiesType_id>1</fkPartiesType_id>
<fName>Jon </fName>
<lName>VanKinsbergen</lName>
<mName>Jim</mName>
<busName>Mutual Of Enumclaw</busName>
<address>1460 Well St</address>
<city>Enumclaw</city>
<state>WA</state>
<zip>98022</zip>
<zip4>1234</zip4>
<phone>8003665551</phone>
<taxID></taxID>
<req1099>N</req1099>
<comments>This is a test</comments>
</party>
<party>
<uid>2</uid>
<fkPartiesType_id>1</fkPartiesType_id>
<fName>Homer</fName>
<lName>Simpson</lName>
<mName>j</mName>
<busName>Burns Nuclear</busName>
<address>1234 Glowing Drive</address>
<city>Springfield</city>
<state>IL</state>
<zip>12345</zip>
<zip4>1234</zip4>
<phone>8005551212</phone>
<taxID></taxID>
<req1099>n</req1099>
<comments>MMMMM Donuts</comments>
</party>
</parties>'
/* END TEST DATA */

EXEC sp_xml_preparedocument @iPartyDoc OUTPUT, @partyXML

/* Now insert the data from the XML into a Table variable */

UPDATE testing
SET fkPartiesType_id = tt.fkPartiesType_id,
fName = tt.fName,
lName = tt.lName,
busName = tt.busName,
address = tt.address,
city = tt.city,
state = tt.state,
zip = tt.zip,
zip4 = tt.zip4,
phone = tt.phone,
taxID = tt.taxID,
req1099 = tt.req1099,
comments = tt.comments,
uiId = tt.uiId,
createDate = tt.createDate
FROM
(SELECT id, fkPartiesType_id, fName, lName, busName, address, city, state, zip, zip4, phone, taxID, req1099, comments, uiId, @sprocTimeStamp AS CreateDate
FROM OPENXML (@iPartyDoc,'parties/party',2)
WITH( id INT,
fkPartiesType_id VARCHAR(50),
fName VARCHAR(50),
lName VARCHAR(50),
busName VARCHAR(50),
address VARCHAR(50),
city VARCHAR(50),
state CHAR(2),
zip CHAR(5),
zip4 CHAR(4),
phone CHAR(10),
taxID CHAR(10),
req1099 CHAR(10),
comments VARCHAR(200),
uiId INT,
createDate DATETIME
)
WHERE id IS NOT NULL
) tt, testing t
WHERE t.id = tt.id

So what happens is for every XML node that has an id, it will update the testing table values where the id's match.

Rick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources