SQL Query

Question

Post reply

SQL Query

isuckatsql

SSChampion

Points: 10381
More actions
August 12, 2008 at 12:34 pm

#192263

I am trying to update a field in a SQL DB that is already populated.
I dont want to replace the data in the field, i want to add to append.
eg. if the field originally contained 'java,asp'
i want to add 'sap'. I now want the field to contain:
'java,asp,sap'
UPDATE pro
set prokw1='oneworld'
FROM pro
inner join res on pro.proln=res.resln and
pro.profn=res.resfn and
pro.Promi=res.resmi
where Resnar like '%oneworld%'
and Resln='miller' and Resfn='brian'
This code exchanges the value but i'm not sure if i need to use 'INSERT'
Thanks

Viewing 15 posts - 1 through 15 (of 22 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Your set statement just to to reference the column you are updating so in your example you would need:

set prokw1= prokw1 + 'oneworld'

You may want a space or comma in there as well.

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

isuckatsql SSChampion Points: 10381 More actions · Answer 2

isuckatsql

SSChampion

Points: 10381

August 12, 2008 at 1:56 pm

#857016

Worked like a charm!

Many Thanks 🙂

isuckatsql SSChampion Points: 10381 More actions · Answer 3

UPDATE pro

set prokw2=prokw2+', ONEW'

FROM pro

inner join res on pro.proln=res.resln and

pro.profn=res.resfn and

pro.Promi=res.resmi

where Resnar like '%oneworld%'

Msg 8152, Level 16, State 13, Line 1

String or binary data would be truncated.

The statement has been terminated.

I am running the above query but get the attached error msg.

I assume this is due to the prokw2 field not having enough space to add ', ONEW'.

How do i test for available space in a field, prior to committing to a write?

Thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

If you know the defined column length in advance you can do something like this:

Update table

Set column = column + 'text'

Where

[criteria here] AND

Len(column + 'text') <= 25 -- insert your defined column width here

Or This:

Update table

Set column = Left(column + 'text', 25) -- 25 being your column width

Where

[criteria here]

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

isuckatsql SSChampion Points: 10381 More actions · Answer 5

I will give that a go! Thanks.

I have another question.

In the table below you can see prokw1 and prokw2 defined with a size of 240 characters.

With all the new features availble these days, is it possible to either increase the field length beyond (240). I cant see that you could do this.

OR could i add a new field in the table, Prokw3 1000 characters ?

I just dont know if you can do these things! In the old COBOL days you could not!

Thanks

23ProsaldtDateShort DateNoYesUserEffective date of current salary or rate

24Prokw1Text240YesNoUserBKeywords

25Prokw2Text240YesNoUserBKeywords overflow

26ProdtDateShort DateNoNoUserDate the profile was added/changed

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Are you working with SQL Server using MS Access? I don't know if you can use the GUI in Access to make the change but the T-SQL for it would be

Alter Table [table_name]

Alter Column Prokw1 [varchar/nvarchar/char/nchar](length)

For char and varchar you can have a length up to 8000 and for varchar you can use varchar(max). For nchar and nvarchar you can have a length up to 4000 with nvarchar(max) being available as well. The max means unlimited.

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

isuckatsql SSChampion Points: 10381 More actions · Answer 7

The application was written in VB and works with Access, but if you want your DB to store a lot of records, they recommend upsizing to SQL.

I am current running on SQL Server 2008 and using SQL Managment Studio to test my changes.

My only downside to changing the size of the prokw1 and prokw2 fields is that they are fixed length on a GUI.

If i create a prokw3 field, it wont be on the GUI, but will enable me to store more index fields.

So i guess i will play with the ALTER command for adding a prokw3 column.

Thanks

Ron McCullough SSC Guru Points: 63877 More actions · Answer 8

Working with VB should not be a problem, simply either increase the length of the text box or go into the properties (f4 key) for the text box and set multi-line to true (assuming you have the physical space on the form to accommodate the larger size). If it is a drop down combo box then your option is to increase the length to accomodate the additional characters or to write code to act as an access drop down combo box does. For your help this works in VB6 not tested in VB net

Option Explicit

Private Declare Function SendMessages Lib "user32" Alias "SendMessageA" (ByVal _

hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

'

Const CB_SETDROPPEDWIDTH = &H160

Public Sub SetComdoDropDownWidth(Combobox As Combobox, ByVal lWidth As Long)

'

Call SendMessages(Combobox.hwnd, CB_SETDROPPEDWIDTH, lWidth, ByVal 0&)

End Sub

Use as

Private Sub Combo1_DropDown()

Call SetComdoDropDownWidth(Combo1, 1500)

End Sub

The above will lengthen the drop down portion of the combo box to allow the user to make a selection and after selections are made will revert to its original size.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

isuckatsql SSChampion Points: 10381 More actions · Answer 9

UPDATE pro

set prokw3=', test5erwin'

FROM pro

inner join res on pro.proln=res.resln and

pro.profn=res.resfn and

pro.Promi=res.resmi

where Resnar like '%erwin%'

and Proln='briggs'

and Profn='connie'

I added a new column 'prokw3' and set nvarchar(440), null and made it an index.

When i run the above query, it tell me it updates records, but shows no data in prokw3.

When i change the query to set prokw3='test5erwin' it works.

Why would the above statement not work for prokw3, yet work for prokw2, which was an exisiting column?

Did I miss something on the column create ?

Thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

I'm not sure what is missing because in the update you list prokw3 should be set to ', test5Erwin'.

In all honesty, based on my limit understanding of what you are trying to do, I think you should re-factor the application to use SQL Server and actually merge your 2 keyword columns into 1 column of nvarchar/varchar(max), although you could get away with (1000) probably. Then create a non-clustered index on that column. That would really only mean changing your application to only have 1 textbox with unlimited entry which is possible.

If my suggestion is not an option you should consider using a computed column instead of a real column which has to be updated. Check out this article on computed columns: http://www.sqlservercentral.com/articles/T-SQL/61764/

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

isuckatsql SSChampion Points: 10381 More actions · Answer 11

I am at the mercy of packaged software!

I dont have access to the source code, so i am trying to work around this situation 🙁

In the new version of the product, they are doing what you sugggested!

I am tryng to improve some of the functionality of the product via SQL.

eg. For me to update the keyword table using their method takes 24 hours, using a SQL query takes less that 30 minutes!

My next goal is to do a direct import into the SQL DB via an Excel or TEXT file.

Their method of parsed import loads an average of 500 records per hour, which is just too slow!

Thanks

isuckatsql SSChampion Points: 10381 More actions · Answer 12

UPDATE PRO

IF Len(Prokw1 + ', HL7') <=240 THEN

SET Prokw1= Prokw1 + ', HL7'

ELSEIF Len(Prokw2 + ', HL7') <=240 THEN

SET Prokw2= Prokw2 + ', HL7'

ELSE SET Prokw3= Prokw3 + ', HL7'

ENDIF

FROM PRO

inner join res on pro.proln=res.resln and

pro.profn=res.resfn and

pro.Promi=res.resmi

where Resnar like '%hl7%'

Will this work in a SQL Query or do i need to use CASE instead of IF ?

Thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 13

You would need a CASE as IF is a control of flow construct and CASE is an Expression.

Have you considered using a computed column as I suggested? I really think that will be the easiest and potentially the best solution.

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

isuckatsql SSChampion Points: 10381 More actions · Answer 14

Thanks, i missed the link, duh!

Not sure how to do nested CASE.

I am trying to do the following.

If the prokw1 is full, goto prokw2.

If the prokw2 is full, goto prokw3.

Why cant you do this ?

CASE

WHEN Len(Prokw2 + ', HL7') <=240

THEN SET Prokw2= Prokw2 + ', HL7'

END

I know this query wont work but i cant find an example of what i want to do.

UPDATE PRO

SET Prokw1=

CASE

WHEN Len(Prokw1 + ', HL7') <=240

THEN Prokw1 + ', HL7'

END

SET Prokw2=

CASE

WHEN Len(Prokw2 + ', HL7') <=240

THEN Prokw2 + ', HL7'

END

SET Prokw3=

CASE

WHEN Len(Prokw3 + ', HL7') <=240

THEN Prokw3 + ', HL7'

END

FROM PRO

INNER JOIN RES on pro.proln=res.resln and

pro.profn=res.resfn and

pro.Promi=res.resmi

WHERE Resnar like '%hl7%'

Thanks