June 9, 2011 at 12:31 pm
I have a table, INVOICES, of which I need to conditionally update 1 of 3 fields based on which one(s) already have a value.
My Table:
CREATE TABLE [dbo].[invoices](
[ID] [int] IDENTITY(1,1) NOT NULL,
[INVNUM] [int] NULL,
[DIVISION] [char](5) NULL,
[NUMERATOR] [char](10) NULL,
[NUMERATOR2] [char](10) NULL,
[NUMERATOR3] [char](10) NULL,
[MEASURE] [smallint] NULL,
CONSTRAINT [INV_ID_KEY] PRIMARY KEY CLUSTERED
)
So I want to SET NUMERATOR = value if NUMERATOR is NULL else I want to SET NUMERATOR2 = value if NUMERATOR2 is NULL else I want to SET NUMERATOR3 = value if NUMERATOR3 is NULL.
I will be getting the value to update with from a SELECT:
SELECT a.CPT
FROM PQRI_main a INNER JOIN
Measures b on a.MEASURE = b.MEASURE INNER JOIN
invoices i on i.INVNUM = a.INVOICE
WHERE a.CPT IN (b.CPT2, b.CPT2_2, b.CPT2_3, b.CPT2_4, b.CPT2_5,
b.CPT2_6, b.CPT2_7,b.CPT2_8, b.CPT2_9, b.CPT2_10)
What is the best way to accomplish this? Thanks for helping this newbie learn. 🙂
Oh, I guess I should add that my select statement could return 3 rows and if so I need to try and update all 3 fields...
June 9, 2011 at 12:48 pm
lgoolsby 86333 (6/9/2011)
I have a table, INVOICES, of which I need to conditionally update 1 of 3 fields based on which one(s) already have a value.My Table:
CREATE TABLE [dbo].[invoices]([ID] [int] IDENTITY(1,1) NOT NULL,
[INVNUM] [int] NULL,
[DIVISION] [char](5) NULL,
[NUMERATOR] [char](10) NULL,
[NUMERATOR2] [char](10) NULL,
[NUMERATOR3] [char](10) NULL,
[MEASURE] [smallint] NULL,
CONSTRAINT [INV_ID_KEY] PRIMARY KEY CLUSTERED
)
So I want to SET NUMERATOR = value if NUMERATOR is NULL else I want to SET NUMERATOR2 = value if NUMERATOR2 is NULL else I want to SET NUMERATOR3 = value if NUMERATOR3 is NULL.
I will be getting the value to update with from a SELECT:
SELECT a.CPTFROM PQRI_main a INNER JOIN
Measures b on a.MEASURE = b.MEASURE INNER JOIN
invoices i on i.INVNUM = a.INVOICE
WHERE a.CPT IN (b.CPT2, b.CPT2_2, b.CPT2_3, b.CPT2_4, b.CPT2_5,
b.CPT2_6, b.CPT2_7,b.CPT2_8, b.CPT2_9, b.CPT2_10)
What is the best way to accomplish this? Thanks for helping this newbie learn. 🙂
Oh, I guess I should add that my select statement could return 3 rows and if so I need to try and update all 3 fields...
I was following you until the last part. See if the following will get you in the right direction.
update invoices set numerator1 = case numerator1 when null then a.cpt else numerator1 end [repeat for additional fields]
from PQRI_main a INNER JOIN
Measures b on a.MEASURE = b.MEASURE INNER JOIN
invoices i on i.INVNUM = a.INVOICE
WHERE a.CPT IN (b.CPT2, b.CPT2_2, b.CPT2_3, b.CPT2_4, b.CPT2_5,
b.CPT2_6, b.CPT2_7,b.CPT2_8, b.CPT2_9, b.CPT2_10)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 9, 2011 at 1:26 pm
Does not work. I am attempting this just on NUMERATOR and all stays NULL. I tried 2 ways:
1:
UPDATE invoices set numerator = CASE NUMERATOR WHEN NULL THEN a.cpt ELSE NUMERATOR END
FROM PQRI_main a INNER JOIN
Measures b on a.MEASURE = b.MEASURE INNER JOIN
invoices i on i.INVNUM = a.INVOICE AND i.MEASURE = a.MEASURE
WHERE a.CPT IN (b.CPT2, b.CPT2_2, b.CPT2_3, b.CPT2_4, b.CPT2_5,
b.CPT2_6, b.CPT2_7, b.CPT2_8, b.CPT2_9, b.CPT2_10)
2:
UPDATE invoices set numerator = CASE NUMERATOR WHEN NULL THEN (
SELECT a.cpt FROM PQRI_main a INNER JOIN
Measures b on a.MEASURE = b.MEASURE INNER JOIN
invoices i on i.INVNUM = a.INVOICE AND i.MEASURE = a.MEASURE
WHERE a.CPT IN (b.CPT2, b.CPT2_2, b.CPT2_3, b.CPT2_4, b.CPT2_5,
b.CPT2_6, b.CPT2_7, b.CPT2_8, b.CPT2_9, b.CPT2_10)) ELSE NUMERATOR END
It returns that 49,000 rows affected which would be correct # of rows and if I do the SELECT I posted in 1st message I get data returned. It simply is not updating the NUMERATOR field.
June 9, 2011 at 1:48 pm
lgoolsby 86333 (6/9/2011)
Does not work. I am attempting this just on NUMERATOR and all stays NULL. I tried 2 ways:1:
UPDATE invoices set numerator = CASE NUMERATOR WHEN NULL THEN a.cpt ELSE NUMERATOR ENDFROM PQRI_main a INNER JOIN
Measures b on a.MEASURE = b.MEASURE INNER JOIN
invoices i on i.INVNUM = a.INVOICE AND i.MEASURE = a.MEASURE
WHERE a.CPT IN (b.CPT2, b.CPT2_2, b.CPT2_3, b.CPT2_4, b.CPT2_5,
b.CPT2_6, b.CPT2_7, b.CPT2_8, b.CPT2_9, b.CPT2_10)
2:
UPDATE invoices set numerator = CASE NUMERATOR WHEN NULL THEN (SELECT a.cpt FROM PQRI_main a INNER JOIN
Measures b on a.MEASURE = b.MEASURE INNER JOIN
invoices i on i.INVNUM = a.INVOICE AND i.MEASURE = a.MEASURE
WHERE a.CPT IN (b.CPT2, b.CPT2_2, b.CPT2_3, b.CPT2_4, b.CPT2_5,
b.CPT2_6, b.CPT2_7, b.CPT2_8, b.CPT2_9, b.CPT2_10)) ELSE NUMERATOR END
It returns that 49,000 rows affected which would be correct # of rows and if I do the SELECT I posted in 1st message I get data returned. It simply is not updating the NUMERATOR field.
You were SO close. Just a slight tweak to the update statement. Need to move the numerator check because of nulls. 😉
UPDATE invoices set numerator = CASE WHEN NUMERATOR IS NULL THEN a.cpt ELSE NUMERATOR END
The second is nowhere near what you want though.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 9, 2011 at 2:12 pm
Works PERFECT!!! Only problem now is that this does not handle when there are more than one records found in my PQRI_main table and thus multiple a.CPT's. When this occurs then I need the 1st one entered into NUMERATOR1 and the second entered into NUMERATOR2. I tried the following code but it seems to just enter the same one into both NUMERATOR and NUMERATOR2. Not sure if I explained the right???
UPDATE i set i.numerator = CASE
WHEN NUMERATOR IS NULL THEN a.cpt ELSE i.NUMERATOR
END ,
i.numerator2 = CASE
WHEN NUMERATOR2 IS NULL AND a.CPT <> NUMERATOR THEN a.cpt ELSE i.NUMERATOR2
END
FROM invoices i INNER JOIN
PQRI_main a on i.INVNUM = a.INVOICE AND i.MEASURE = a.MEASURE INNER JOIN
Measures b on a.MEASURE = b.MEASURE
WHERE a.CPT IN (b.CPT2, b.CPT2_2, b.CPT2_3, b.CPT2_4, b.CPT2_5,
b.CPT2_6, b.CPT2_7, b.CPT2_8, b.CPT2_9, b.CPT2_10)
Do I somehow need to loop through the PQRI_main table and for each record UPDATE the appropriate INVOICES record? Lost on this one.
June 9, 2011 at 2:33 pm
So for now, my solution is to run 3 UPDATE scripts. Each will UPDATE a different NUMERATOR field as follows. The check is in the WHERE statement.
UPDATE i set i.numerator = CASE
WHEN NUMERATOR IS NULL THEN a.cpt ELSE i.NUMERATOR
END
FROM invoices i INNER JOIN
PQRI_main a on i.INVNUM = a.INVOICE AND i.MEASURE = a.MEASURE INNER JOIN
Measures b on a.MEASURE = b.MEASURE
WHERE a.CPT IN (b.CPT2, b.CPT2_2, b.CPT2_3, b.CPT2_4, b.CPT2_5, b.CPT2_6,
b.CPT2_7, b.CPT2_8, b.CPT2_9, b.CPT2_10)
UPDATE i set i.numerator2 = CASE
WHEN NUMERATOR2 IS NULL THEN a.cpt ELSE i.NUMERATOR2
END
FROM invoices i INNER JOIN
PQRI_main a on i.INVNUM = a.INVOICE AND i.MEASURE = a.MEASURE INNER JOIN
Measures b on a.MEASURE = b.MEASURE
WHERE a.CPT IN (b.CPT2, b.CPT2_2, b.CPT2_3, b.CPT2_4, b.CPT2_5, b.CPT2_6,
b.CPT2_7, b.CPT2_8, b.CPT2_9, b.CPT2_10)
AND (a.CPT <> i.NUMERATOR)
UPDATE i set i.numerator3 = CASE
WHEN NUMERATOR3 IS NULL THEN a.cpt ELSE i.NUMERATOR3
END
FROM invoices i INNER JOIN
PQRI_main a on i.INVNUM = a.INVOICE AND i.MEASURE = a.MEASURE INNER JOIN
Measures b on a.MEASURE = b.MEASURE
WHERE a.CPT IN (b.CPT2, b.CPT2_2, b.CPT2_3, b.CPT2_4, b.CPT2_5, b.CPT2_6,
b.CPT2_7, b.CPT2_8, b.CPT2_9, b.CPT2_10)
AND (a.CPT <> i.NUMERATOR AND a.CPT <> i.NUMERATOR2)
June 9, 2011 at 2:39 pm
If this is a one time update I would say that is probably the easiest way for you to handle it. If this is an ongoing process then you will probably need to explore other ways. I have some ideas on how to do it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 9, 2011 at 2:45 pm
It will run on a weekly basis. I have just converted a windows app from nonSQL to SQL. Currently this is done inside the application code and I am slowly working to move some code into SQL for speed and performance and ability to use other clients. Working with hundreds of thousands of records, tasks like this are soooo much faster in SQL. Any ideas you have will be greatly appreciated. I am kinda learning SQL as I go since I was thrown into it. Having lots of fun!
June 9, 2011 at 3:02 pm
I think you can incorporate the ROW_NUMBER from the other code thing you were working on a couple days ago. But not really sure how your data looks to be sure. Otherwise you might have to join to the same tables more than once so you can keep track of which cpt is slated for which field. Sounds like some architecture issues are going to haunt you if this is the type of update you have to do routinely.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply