Cannot figure out my update correctly

Question

Cannot figure out my update correctly

Oracle765

SSCrazy

Points: 2862
More actions
September 15, 2013 at 6:24 pm

#282533

Hi professionals
I have a table like so
departmentid,Barcode,Invoiceno,expirydate
2036,xyz,12345,23/01/2013
2077,xyz,56245,05/05/2012
2036,abc,67654,02/11/2012
my employer wants me to update the table column invoiceno and concatonate the invoiceno column with barcode column so i can then free up the barcode column so they can enter some other criteria based on our 2036 departmentid. so the new updated table should show
departmentid,Barcode,Invoiceno,expirydate
2036,12345 xyz,NOW EMPTY,23/01/2013
2077,xyz,56245,05/05/2012
2036,67654 abc,NOW EMPTY,02/11/2012
here is my initial code to update the invoice column which shows an error "Incorrect Syntax near L1"
update licensedetails L1
set L1.invoiceno = (select L2.barcode + ' ' + L2.invoiceno
from licensedetails L2
where L1.departmentid = L2.departmentid
and L2.departmentid = '2036')
am i going wrong somewhere
thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86908 More actions · Answer 1

Oracle765 (9/15/2013)
am i going wrong somewhere

Yes, I'd say the idea of packing two separate attributes of your invoices into a single column could be considered "wrong." Is there a reason why you can't just create a new column in this table to store that new attribute?

Otherwise, perhaps this might be what you're looking for:

CREATE TABLE #SampleData

(

departmentid INT

,Barcode VARCHAR(100)

,Invoiceno VARCHAR(100)

,expirydate DATE

);

INSERT INTO #SampleData

SELECT 2036,'xyz','12345','01/23/2013'

UNION ALL SELECT 2077,'xyz',56245,'05/05/2012'

UNION ALL SELECT 2036,'abc',67654,'11/02/2012';

SELECT * FROM #SampleData;

UPDATE #SampleData

SET Barcode=Invoiceno + ' ' + Barcode, InvoiceNo = NULL;

SELECT * FROM #SampleData;

GO

DROP TABLE #SampleData;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

mickyT SSChampion Points: 10360 More actions · Answer 2

Ditto on Dwain's comment, not a good idea.

You may cause yourself more issues down the line following this particular plan. For example are there any current queries or views that will be broken by the fact the invoiceno has been changed. Invoiceno also appears to be a candidate for doing searchs on, so queries on that will need to start using a like clause. Result from queries will need to handle the fact there is a barcode first in the output, so some parsing will need to happen.

Additionally if you do decide to proceed, make sure that you have enough room in your invoiceno column (assuming it is a varchar) to contain the barcode and invoiceno. Also you will need to handle NULLs if you have any in either of the columns.

Oracle765 SSCrazy Points: 2862 More actions · Answer 3

ok thanks for the update, the query works but yes I agree, i will speak to the powers that be, thanks again all

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 4

Oracle765 (9/15/2013)
ok thanks for the update, the query works but yes I agree, i will speak to the powers that be, thanks again all

You could also make a persisted calculated column or a view to do this on a regular basis.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)