May 13, 2008 at 2:35 am
Hello
I would like to write a script that outputs update statements;
For example, if I have a table with ID, Name, Phone ans 3 records:
1, John, 555666777
2, Lynn, 666777888
3, Mary, 777666888
I would like to output the following statements:
Update Clients set phone = xxx where ID = 1
Update Clients set phone = yyy where ID = 2
Update Clients set phone = zzz where ID = 3
(Don't as why I don't do one update statement 🙂 I really need to have all the updates of each row:) )
Thanks!!
May 13, 2008 at 8:09 am
It's a little unclear, without more information.
For example:
- Do you already know ahead of time what value phone will be updated to? If not, how is this determined?
- Are you trying to update every record in the table in sequence, in the same manner you listed? If not, how do you determine which record to update (in other words, how do you determine the value of ID in your where clause)?
Some more representative data (current and expected output) would probably generate the help you seek a little easier.
"Got no time for the jibba jabba!"
-B.A. Baracus
May 13, 2008 at 8:19 am
Actually the update statement is a bit more complicated.
Let's say that i get the phone number from another embedded select (that's why i put values likes XXX and YYY).
The purpose of doing this, is that in my table, the phone is a unique key. So if i do the update on the whole table in one update statement, i sometime get the same phone number twice and the update exists with an error.
With N distinct update statements generated by a script, the statement that generates a unique key index error will be skipped and will move to the next one.
I hope i was clear.
Thanks again
May 13, 2008 at 8:28 am
Looks to me like you just need 1 update statement in a stored procedure like this:
[font="Courier New"]CREATE PROCEDURE update_phone_no
(
@id INT,
@phone VARCHAR(15)
)
AS
UPDATE Clients
SET phone = @phone
WHERE
id = @id[/font]
Then run a script that creates the sp calls.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2008 at 8:38 am
Terry (5/13/2008)
Actually the update statement is a bit more complicated.Let's say that i get the phone number from another embedded select (that's why i put values likes XXX and YYY).
The purpose of doing this, is that in my table, the phone is a unique key. So if i do the update on the whole table in one update statement, i sometime get the same phone number twice and the update exists with an error.
With N distinct update statements generated by a script, the statement that generates a unique key index error will be skipped and will move to the next one.
I hope i was clear.
Thanks again
Sorry my last post was being written while you posted this one. It would really help if you posted your table schema(s) and what you really need to do instead of an over-simplified example. If you check out the article noted in my signature line and post using some of those suggestions it is much easier for others to provide solutions to your real problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2008 at 8:48 am
yes, this is what i was doing.
but as i explained, if i have a unique index error, the transaction is rolled back, and the update doesnt take place.
While if i have all the updates statements, only the update statement that causes unique index error will not be executed...
May 13, 2008 at 8:57 am
sorry, i replied while u where also posting 😉
Here is another example with more realistic tables and code:
table SOURCE contains clients, with the product they hold (MODEL_NO)
since they manually enter data, some product codes are wrong. So i have another table PRODUCT where for each wrong product, corresponds a correct one.
The problem is that sometimes, 2 wrong product have the same right product.
And the client who has these 2 wrong products, will cause a unique index error, and the update is rolled back.
BEGIN TRY
BEGIN TRANSACTION
UPDATE SOURCE
SET MODEL_NO = P.NEW_MODEL
FROM SOURCE INNER JOIN PRODUCT P ON (MODEL_NO = P.OLD_MODEL)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
print ERROR_MESSAGE()
ROLLBACK TRANSACTION
END
END CATCH
If I write distinct updates, only the one that gives unique index error will be rolled... no?
May 13, 2008 at 8:58 am
** edit -- posted while you were posting the real code **
Can you post the original code so we can actually see what you were doing and propose a solution to the real problem?
Ideally there is a way to do it in a set-based fashion, but without the schema we can't provide an answer other than guesses.
Each of your update statements will have to either be in a batch (GO between each) or in a Begin Transaction Commit/RollBack Transaction in order for only the one(s) that fail to rollback and the others to commit.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2008 at 9:03 am
Exactly! how can i do each statement in a transaction?
May 13, 2008 at 9:22 am
Terry (5/13/2008)
Exactly! how can i do each statement in a transaction?
Begin Try
Begin Transaction
Update source set model = 1 where client 2
COmmit Transaction
End Try
Begin Catch
Rollback transaction
End Catch
Begin Try
Begin Transaction
UPdate source set model = 2 where client 3
COmmit Transaction
End Try
Begin Catch
Rollback transaction
End Catch
Terry (5/13/2008)
sorry, i replied while u where also posting 😉Here is another example with more realistic tables and code:
table SOURCE contains clients, with the product they hold (MODEL_NO)
since they manually enter data, some product codes are wrong. So i have another table PRODUCT where for each wrong product, corresponds a correct one.
The problem is that sometimes, 2 wrong product have the same right product.
And the client who has these 2 wrong products, will cause a unique index error, and the update is rolled back.
BEGIN TRY
BEGIN TRANSACTION
UPDATE SOURCE
SET MODEL_NO = P.NEW_MODEL
FROM SOURCE INNER JOIN PRODUCT P ON (MODEL_NO = P.OLD_MODEL)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
print ERROR_MESSAGE()
ROLLBACK TRANSACTION
END
END CATCH
If I write distinct updates, only the one that gives unique index error will be rolled... no?
Based on this code your problem is not in the duplicate new model numbers, but because you have the same client in SOURCE multiple times with OLd Models that are being changed to the same model like this:
Client Old Model New Model
1 1 10
1 2 10
2 1 10
3 3 11
Where the bolded first 2 rows are causing your problem.
Here is code that I am pretty sure will work for you:
[font="Courier New"]-- create test tables
CREATE TABLE #source
(
client INT,
model INT
)
CREATE UNIQUE INDEX ux_source ON #source(client, model)
CREATE TABLE #product
(
old_model INT,
new_model INT
)
-- generate test data
INSERT INTO #source
SELECT
1,
1
UNION
SELECT
1,
2
UNION
SELECT
2,
1
UNION
SELECT
3,
3
INSERT INTO #product
SELECT
1,
10
UNION
SELECT
2,
10
UNION
SELECT
3,
11
-- do th update
UPDATE #source
SET model = (SELECT new_model FROM #product WHERE model = old_model)
WHERE
model IN (SELECT MIN(model) FROM #source GROUP BY client) -- only updates 1 record for each client/model combination
SELECT @@ROWCOUNT AS updated_rows -- should be 3 in the test data
-- show the updated rows
SELECT
*
FROM
#source
-- delete the record that would have been a dupe
DELETE
FROM
#source
WHERE
model IN (SELECT old_model FROM #product)
SELECT @@ROWCOUNT AS deleted_rows -- should be 1 in the test data
-- show the final outcome
SELECT
*
FROM
#source
-- clean up
DROP TABLE #source
DROP TABLE #product
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2008 at 9:31 am
This is exactly the case! I will try that
Thank you loads!! 🙂
May 15, 2008 at 2:18 am
Hello
I've tried what you suggested, it works, only if the following statement always returns a value
SELECT new_model FROM #product WHERE model = old_model
But in the case where old models don't have a new model in the product table, i still get a unique index error...
To be sure of it, how can I catch the exact record that is causing the error?
print ERROR_MESSAGE() + something ???
Thanks
May 15, 2008 at 7:49 am
Okay the first solution assumed you had no records with the correct model try this:
[font="Courier New"]-- create test tables
CREATE TABLE #source
(
client INT,
model INT
)
CREATE UNIQUE INDEX ux_source ON #source(client, model)
CREATE TABLE #product
(
old_model INT,
new_model INT
)
-- generate test data
INSERT INTO #source
SELECT
1,
1
UNION
SELECT
1,
2
UNION
SELECT
2,
1
UNION
SELECT
3,
3
UNION
SELECT
1,
10
UNION
SELECT
2,
2
INSERT INTO #product
SELECT
1,
10
UNION
SELECT
2,
10
UNION
SELECT
3,
11
-- get records that are all set (model = new_model)
;WITH cteAllSet AS
(
SELECT
S.client,
P.old_model
FROM
#source S JOIN
#product P ON
S.model = P.new_model
)
-- delete any client records where model != new_model
-- and there is a correct record for client - model
DELETE S
FROM
#source S JOIN
cteAllSet C ON
S.client = C.client AND
S.model = C.old_model
-- should be 2
SELECT @@ROWCOUNT AS deleted_because_existing_record
-- get the records we want to update
;WITH cteUpdate AS
(
SELECT
S.client,
MIN(P.old_model) AS old_model,
P.new_model
FROM
#source S JOIN
#product P ON
S.model = P.old_model
GROUP BY
S.client,
P.new_model
)
-- do the update
UPDATE #source
SET model = (SELECT new_model FROM cteUpdate WHERE model = old_model)
WHERE
client IN (SELECT client FROM cteUpdate) AND
model IN (SELECT old_model FROM cteUpdate)
SELECT @@ROWCOUNT AS updated_rows -- should be 3 in the test data
-- show the updated rows
SELECT
*
FROM
#source
-- delete the record that would have been a dupe
DELETE
FROM
#source
WHERE
model IN (SELECT old_model FROM #product)
SELECT @@ROWCOUNT AS deleted_rows -- should be 1 in the test data
-- show the final outcome
SELECT
*
FROM
#source
-- clean up
DROP TABLE #source
DROP TABLE #product[/font]
I changed the code to use CTE's for readability.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 15, 2008 at 8:08 am
Ok the code works, but I don't want to delete records from the source table :ermm:
Do you suggest that I copy all the a temp table , delete from the temp table, then copy to real table?! I think this is quite complicated...
May 15, 2008 at 8:10 am
Oh, i think i misunderstood the delete part :blush:
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply