Hepl with a stored procedure

I want to write a stored procedure that takes two params and merges records
whose id match the two params.
For example, I have a TableX(col1 char(10), col2 char(5), col3 int) with 4
rows:
"c1"," i2", 3
"c1", "i4", 2
"d1", "i2", 2
"d1", "i3", 1
After I call the sp and pass "c1" and "d1" as arguments, the table should
contains only 3 rows:
"d1", "i2", 5
"d1", "i3", 1
"d1", "i4", 2
Note: the sp should also take into account that only records with "c1" id
are guaranteed to exist in the table but the records with "d1" is are not.
I could use two cursors to fetch records that match the given col1
arguemtns, do some comparison as I step through the cursors, write the
results into a temp table, drop the records in TableX, and finally select
records from temp table into TableX.
I think there must be an elegant & efficient way that uses only subqueries
and maybe a Table variable. Could any one help me with this?.... a complex (or simple) update statement that has the same logic as your
query.
I'd most likely use executeSQL within my sProc that allows output params fro
m an
exec string to do further validation processing
Then start a transaction, first do the update and then the delete then close
the
transaction.
I would also be concerned about record locks if users are in these same tabl
es
during this update/merge.
HTH
JeffP....
"VC" <vutha@.mailblocks.com> wrote in message
news:eAM7%238tEFHA.464@.TK2MSFTNGP15.phx.gbl...
> I want to write a stored procedure that takes two params and merges record
s
> whose id match the two params.
> For example, I have a TableX(col1 char(10), col2 char(5), col3 int) with 4
> rows:
> "c1"," i2", 3
> "c1", "i4", 2
> "d1", "i2", 2
> "d1", "i3", 1
> After I call the sp and pass "c1" and "d1" as arguments, the table should
> contains only 3 rows:
> "d1", "i2", 5
> "d1", "i3", 1
> "d1", "i4", 2
> Note: the sp should also take into account that only records with "c1" id
> are guaranteed to exist in the table but the records with "d1" is are not.
> I could use two cursors to fetch records that match the given col1
> arguemtns, do some comparison as I step through the cursors, write the
> results into a temp table, drop the records in TableX, and finally select
> records from temp table into TableX.
> I think there must be an elegant & efficient way that uses only subqueries
> and maybe a Table variable. Could any one help me with this?
>|||On Mon, 14 Feb 2005 14:49:28 -0700, VC wrote:

>I want to write a stored procedure that takes two params and merges records
>whose id match the two params.
>For example, I have a TableX(col1 char(10), col2 char(5), col3 int) with 4
>rows:
>"c1"," i2", 3
>"c1", "i4", 2
>"d1", "i2", 2
>"d1", "i3", 1
> After I call the sp and pass "c1" and "d1" as arguments, the table should
>contains only 3 rows:
>"d1", "i2", 5
>"d1", "i3", 1
>"d1", "i4", 2
>Note: the sp should also take into account that only records with "c1" id
>are guaranteed to exist in the table but the records with "d1" is are not.
Hi VC,
This can be done with three queries. Your sp should enclose them in a
procedure and add proper error handling.
-- Handle c1 without matching d1
-- (these are simply "renamed" to d1)
UPDATE c
SET col1 = 'd1'
FROM TableX AS c
LEFT JOIN TableX AS d
ON d.col1 = 'd1'
AND d.col2 = c.col2
WHERE c.col1 = 'c1'
-- Handle c1 with matching d1
-- (col3 in the d1 row gets increased; the c1 row is left unchanged)
UPDATE d
SET col3 = d.col3 + c.col3
FROM TableX AS c
INNER JOIN TableX AS d
ON d.col1 = 'd1'
AND d.col2 = c.col2
WHERE c.col1 = 'c1'
-- Remove remaining c1 rows
DELETE TableX
WHERE col1 = 'c1'
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank for the suggestion of using the transaction to protect the db
intergrity in case of errors. In my case, record locks should not be a
problem because col1 contains guid variables that are supposed to be
uniquely created for a user session. So the operation would affect, if any,
just a few rows that belong to one user.
BTW, check out the solution provided by Hugo Kornelis. It is much more
efficient than the one I had in mind.
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23QhIYQuEFHA.1396@.tk2msftngp13.phx.gbl...
> ... a complex (or simple) update statement that has the same logic as
> your
> query.
> I'd most likely use executeSQL within my sProc that allows output params
> from an
> exec string to do further validation processing
> Then start a transaction, first do the update and then the delete then
> close the
> transaction.
> I would also be concerned about record locks if users are in these same
> tables
> during this update/merge.
> HTH
> JeffP....
> "VC" <vutha@.mailblocks.com> wrote in message
> news:eAM7%238tEFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||Your solution works nicely. I just make a small change to your block of code
so that only rows with unmatched col2 are renamed.
UPDATE c
SET col1 = 'd1'
FROM T1 AS c
WHERE c.col1 = 'c1'
AND c.col2 NOT IN
(SELECT d.col2 FROM T1 AS d
WHERE d.col1 = 'd1')
Thank you very much. I now only need to create a sp out of these codes :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:aib2111n1uigknubv8kk4c1lmk1j0qc02m@.
4ax.com...
> On Mon, 14 Feb 2005 14:49:28 -0700, VC wrote:
>
> Hi VC,
> This can be done with three queries. Your sp should enclose them in a
> procedure and add proper error handling.
> -- Handle c1 without matching d1
> -- (these are simply "renamed" to d1)
> UPDATE c
> SET col1 = 'd1'
> FROM TableX AS c
> LEFT JOIN TableX AS d
> ON d.col1 = 'd1'
> AND d.col2 = c.col2
> WHERE c.col1 = 'c1'
> -- Handle c1 with matching d1
> -- (col3 in the d1 row gets increased; the c1 row is left unchanged)
> UPDATE d
> SET col3 = d.col3 + c.col3
> FROM TableX AS c
> INNER JOIN TableX AS d
> ON d.col1 = 'd1'
> AND d.col2 = c.col2
> WHERE c.col1 = 'c1'
> -- Remove remaining c1 rows
> DELETE TableX
> WHERE col1 = 'c1'
> (untested)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 15 Feb 2005 11:54:58 -0700, VC wrote:

>Your solution works nicely. I just make a small change to your block of cod
e
>so that only rows with unmatched col2 are renamed.
>UPDATE c
>SET col1 = 'd1'
>FROM T1 AS c
>WHERE c.col1 = 'c1'
>AND c.col2 NOT IN
> (SELECT d.col2 FROM T1 AS d
> WHERE d.col1 = 'd1')
Hi VC,
This statement is actually equivalent to the statement I intened to use,
but I now see that I forgot to include one important line. This is the
statement as I meant it to write:
UPDATE c
SET col1 = 'd1'
FROM TableX AS c
LEFT JOIN TableX AS d
ON d.col1 = 'd1'
AND d.col2 = c.col2
WHERE c.col1 = 'c1'
AND d.col1 IS NULL -- This line is added
The extra line is there to test that the LEFT JOIN did not find a matching
row in TableX.
The main advantage of my version over yours is that NOT IN will produce
unexpected results if any of the rows in your table can have a NULL value
for col2. That's why I always use either the LEFT JOIN technique, or a
subquery with EXISTS.
Of course, the problem with the LEFT JOIN technique is that it goes
dramatically wrong if you forget to include the IS NULL test... <g>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help: Query

Hi,
I have 2 tables like below
CREATE TABLE Location
(
LocationID int NOT NULL,
LocationName char (30) NOT NULL UNIQUE,
CONSTRAINT PK_Location PRIMARY KEY (LocationID)
)
CREATE TABLE Parts
(
PartID int NOT NULL,
LocationID int NOT NULL,
PartName char (30) NOT NULL,
CONSTRAINT PK_Parts PRIMARY KEY (PartID),
CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
REFERENCES Location (LocationID)
)
I have created a stored procedure to delete all of partid on a certain
location as shown below.
CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
BEGIN
DECLARE @.PartID int
DECLARE crs_Parts CURSOR FOR
SELECT p.PartID
FROM Parts AS p INNER JOIN Location AS 1
ON p.LocationID = @.LocName
WHERE l.LocationName = @.LocName
OPEN crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
DELETE Parts WHERE CURRENT OF crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
END
CLOSE crs_Parts
DEALLOCATE crs_Parts
DELETE Location WHERE LocationName = @.LocName
END
My Question is how to replace the cursor operation in the stored
procedure by using single DELETE statement?
Thanks
Robert LieHi Robert
Jusr the query this way,
DELETE Parts
FROM Parts INNER JOIN Location
ON ON Parts.LocationID = Location.LocationID
WHERE Location.LocationName = @.LocName
hope this is the one that you are looking for:
thanks and regards
Chandra
"Robert Lie" wrote:
> Hi,
> I have 2 tables like below
> CREATE TABLE Location
> (
> LocationID int NOT NULL,
> LocationName char (30) NOT NULL UNIQUE,
> CONSTRAINT PK_Location PRIMARY KEY (LocationID)
> )
>
> CREATE TABLE Parts
> (
> PartID int NOT NULL,
> LocationID int NOT NULL,
> PartName char (30) NOT NULL,
> CONSTRAINT PK_Parts PRIMARY KEY (PartID),
> CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
> REFERENCES Location (LocationID)
> )
>
> I have created a stored procedure to delete all of partid on a certain
> location as shown below.
> CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
> BEGIN
> DECLARE @.PartID int
> DECLARE crs_Parts CURSOR FOR
> SELECT p.PartID
> FROM Parts AS p INNER JOIN Location AS 1
> ON p.LocationID = @.LocName
> WHERE l.LocationName = @.LocName
> OPEN crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> DELETE Parts WHERE CURRENT OF crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> END
> CLOSE crs_Parts
> DEALLOCATE crs_Parts
> DELETE Location WHERE LocationName = @.LocName
> END
> My Question is how to replace the cursor operation in the stored
> procedure by using single DELETE statement?
> Thanks
> Robert Lie
>