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)