Using Update For Modifying Text
Aug 27, 2003
Hello All,
I would like to update some values in my table that have this value: XXXX-XXXX. I would like to update the '-' and change it to a '_'. Does anyone know the best way to do this?
I tried this but I get a "Subquery returned more than 1 value error"
UPDATE PS_MTCH_RULES
SET MATCH_RULE_ID = (SELECT SUBSTRING (MATCH_RULE_ID,1,4)+'_'+SUBSTRING(MATCH_RULE_ID,6, 4) FROM PS_MTCH_RULES WHERE MATCH_RULES_ID <> 'ERS')
Any assistance would be appreciated.
Thank you.
E David Ramos
View 1 Replies
ADVERTISEMENT
Jul 20, 2005
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies
View Related
Sep 8, 2004
Hi all,
I have a table with millions of records, table has three fields: case_id,line_no and notedata field, notedata field is 60 chars long, datatype varchar.for each case_id there could be as many as 2000 line_no meaning 2000 notes. I need to compress these notes into one note by case_no, For example case_no 1 could have 2000 lines of notes but my comressed table shoul have only one line containing all 2000 notes in line_no sequence.
my compressed table contains two fields case_no and notetext, notetext is a text field.
here is the script I am trying to use to accomplish the task but it does not append more than 8000 chars in one case, so my notes are chopping of, how should I do this, please let me know of any suggestions..
Thanks.
truncate table eldoecinotescompressed
insert into eldoecinotescompressed (app_code, case_no)
select distinct app_code, substring(system_key, 6,8)
from eldoecinotes
DECLARE @case VARCHAR(20);
DECLARE @note VARCHAR(80);
DECLARE @lineno VARCHAR(5);
DECLARE notes_cursor CURSOR FOR
select substring(system_key, 6,8) case_no, line_no, rtrim(notedata) notedata FROM EldoECINotes
where substring(system_key, 6,8)<>''
order by 1,2;
OPEN notes_cursor;
FETCH NEXT FROM notes_cursor into @case, @lineno, @note;
WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN TRANSACTION;
update eldoecinotescompressed
set notetext =
(case when isnull(datalength(notetext), 0) >= 0 then
substring(isnull(notetext,''), 1, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 8000 then
substring(isnull(notetext,''), 8001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 16000 then
substring(isnull(notetext,''), 16001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 24000 then
substring(isnull(notetext,''), 24001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 32000 then
substring(isnull(notetext,''), 32001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 40000 then
substring(isnull(notetext,''), 40001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 48000 then
substring(isnull(notetext,''), 48001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 56000 then
substring(isnull(notetext,''), 56001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 64000 then
substring(isnull(notetext,''), 64001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 72000 then
substring(isnull(notetext,''), 72001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 80000 then
substring(isnull(notetext,''), 80001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 88000 then
substring(isnull(notetext,''), 88001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 96000 then
substring(isnull(notetext,''), 96001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 104000 then
substring(isnull(notetext,''), 104001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 112000 then
substring(isnull(notetext,''), 112001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 120000 then
substring(isnull(notetext,''), 120001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 128000 then
substring(isnull(notetext,''), 128001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 136000 then
substring(isnull(notetext,''), 136001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 144000 then
substring(isnull(notetext,''), 144001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 152000 then
substring(isnull(notetext,''), 152001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 0 then
char(13) + char(10)
else '' end) +
isnull(@note,'')
where case_no=@case;
commit;
FETCH NEXT FROM notes_cursor into @case, @lineno, @note;
END
CLOSE notes_cursor;
DEALLOCATE notes_cursor;
View 3 Replies
View Related
Sep 16, 2004
hi
this is probably a piece of pie to fix but i be having some problems......
i have this peice of code to update the sql dataapadapter
Dstest1.Tables("test").Rows(0)("hello") = Me.textboxhello.Text
SqlDataAdapter1.Update(Dstest1, "test")
this does not work, it does work however if i replace the Me.textboxhello.Text
with a value in quotation marks or with another text box not linked to that feild in the table... it does not work with the relevant text box!!!!!!!!
thanks for all your help
cq
View 1 Replies
View Related
Feb 19, 2004
First i use openxml to get my data to update the other server with webservices.
my prob is that i cant update text Fields because i got an error
================
OLE DB provider 'OpenXML' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'OpenXML' IRowset::RestartPosition returned 0x80004005: The provider did not give any information about the error.].
================
what would be my best shot here...
thanx
=======================================
XEC sp_xml_preparedocument @handle OUTPUT, @data
begin transaction
SELECT * FROM TblEvenement WHERE idEvenement = 95
UPDATE TblEvenement SET
TblEvenement.idEvenement = isnull(iox.idEvenement,TblEvenement.idEvenement),
TblEvenement.sNomEvenement = isnull(iox.sNomEvenement,TblEvenement.sNomEvenement),
TblEvenement.sDescriptionCourte = isnull(iox.sDescriptionCourte,TblEvenement.sDescriptionCourte),
TblEvenement.sDescriptionLongue = isnull(iox.sDescriptionLongue,TblEvenement.sDescriptionLongue),
TblEvenement.IdTypeSalle = isnull(iox.IdTypeSalle,TblEvenement.IdTypeSalle),
/*TblEvenement.imgSalle = isnull(iox.imgSalle,TblEvenement.imgSalle),*/
TblEvenement.sNomArtiste = isnull(iox.sNomArtiste,TblEvenement.sNomArtiste),
TblEvenement.bAfficherInternet = isnull(iox.bAfficherInternet,TblEvenement.bAfficherInternet),
TblEvenement.nNbBilletLimite = isnull(iox.nNbBilletLimite,TblEvenement.nNbBilletLimite),
TblEvenement.bLivraisonCourrier = isnull(iox.bLivraisonCourrier,TblEvenement.bLivraisonCourrier),
TblEvenement.IdTypeRepresentation = isnull(iox.IdTypeRepresentation,TblEvenement.IdTypeRepresentation),
TblEvenement.sDetailInternet = isnull(iox.sDetailInternet,TblEvenement.sDetailInternet),
TblEvenement.bHistorique = isnull(iox.bHistorique,TblEvenement.bHistorique),
TblEvenement.bAdmissionGenerale = isnull(iox.bAdmissionGenerale,TblEvenement.bAdmissionGenerale),
TblEvenement.bEvenementEnVente = isnull(iox.bEvenementEnVente,TblEvenement.bEvenementEnVente),
TblEvenement.IdProducteur = isnull(iox.IdProducteur,TblEvenement.IdProducteur),
TblEvenement.bEvenementDemo = isnull(iox.bEvenementDemo,TblEvenement.bEvenementDemo),
/*TblEvenement.sLogoBOCA = isnull(iox.sLogoBOCA,TblEvenement.sLogoBOCA),*/
/*TblEvenement.sLogoLP2722 = isnull(iox.sLogoLP2722,TblEvenement.sLogoLP2722),*/
TblEvenement.sDescriptionCourte_En = isnull(iox.sDescriptionCourte_En,TblEvenement.sDescriptionCourte_En),
/*TblEvenement.sDescriptionLongue_En = isnull(iox.sDescriptionLongue_En,TblEvenement.sDescriptionLongue_En),*/
TblEvenement.sDetailInternet_En = isnull(iox.sDetailInternet_En,TblEvenement.sDetailInternet_En)
FROM OPENXML (@handle, N'//TblEvenement')
WITH
(
idEvenement int,
sNomEvenement nvarchar (100),
sDescriptionCourte nvarchar (100),
sDescriptionLongue text,
IdTypeSalle int,
imgSalle image,
sNomArtiste nvarchar (100),
bAfficherInternet bit,
nNbBilletLimite int,
bLivraisonCourrier bit,
IdTypeRepresentation int,
sDetailInternet nvarchar (100),
bHistorique bit,
bAdmissionGenerale bit,
bEvenementEnVente bit,
IdProducteur int,
bEvenementDemo bit,
sLogoBOCA text,
sLogoLP2722 text,
sDescriptionCourte_En nvarchar (100),
sDescriptionLongue_En text,
sDetailInternet_En nvarchar (100),
sNomEvenement_En nvarchar (100)
) iox
WHERE TblEvenement.idEvenement = iox.IdEvenement /*and WRITETEXT (TblEvenement.sDescriptionLongue @ptrval 'Salut')*/
SELECT * FROM TblEvenement WHERE idEvenement = 95
rollback
EXEC sp_xml_removedocument @handle
View 1 Replies
View Related
Jul 22, 2004
I would like to update a field that already has data in it and I dont' want to overwrite the existing text. Here is my existing statement
UPDATE wr SET cf_notes = " + tmp_array(24) + " WHERE wr_id = " + data_temp(0)
I would like to add cf_notes + tmp_array(24) to cf_notes. Is this possible in SQL? If so, what is the correct syntax. I have tried 6 different statements and I get a compile error on every statement.
Thanks,
SBR
View 1 Replies
View Related
Aug 29, 2006
Hello Everyone,
We receive this error with ADO.NET (on a SQL Server 2000):
The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ?OnExecuteNonQuery@dtbmsq_Statement_c@@$$FUAE?AW4dtb_Result_t@@PAUdtbsql_Connection_ip@@PAH@Z(dtbmsq_Statement_c* , dtbsql_Connection_ip* Connection, Int32* NbRowsAffected) in s:ogl20061srcodbmsqdtbmsqdtbmsq_statement_c.cpp:line 598
N.B. The update concerned only update one row.
Is it a known restriction? Is it a restriction from ADO.NET or from the database server?
Is it documented somewhere?
Regards.
Carl
View 6 Replies
View Related
Sep 27, 2007
Good Morning Folks,
Pretty new to Mysql, and have a query that if anyone can help me with id be very grateful!!
I am using the UPDATE command and wish to copy a columns data to another column, but the first column contains text then (text in a bracket) i wish to copy everything from the first column except whats in the bracket to column 2???
example...
UPDATE table_name SET `field1` = `field2`...............???
Any ideas??
Many Thanks
View 3 Replies
View Related
Dec 15, 2003
I tryed to update tables part of my MSDE database, using the SqlDataAdapter.Update() method. It worked fine untill I tryed to update a table that has a Column with the Text SQL DataType. It didn't work. The error was :
"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."
Is there a way to do it ?
Thanks,
Jeff
View 4 Replies
View Related
Jan 17, 2005
First I recieve this error when I upload a photo and click on the save button.
'ODBC - update on a linked table 'PersonMisc' failed.'
Then this error appears after I click ok.
[Microsoft][ODBC SQL Server Driver][SQL Server] The READTEXT and WRITETEXT statements cannot be used with views[#285][Microsoft][ODBC SQL Server Driver] Warning: Partial insert/update The insert/update of a text or image did not succeed.[#0].
Is there a size limit on the pictures I am uploading?
View 2 Replies
View Related
Mar 16, 2015
I am trying my first bulk update to an existing SWL table from a CSV text file,The text file naming is exacrtly the same as the SQL table, with the same attributes
The statements:
BULK INSERT [Jedox_prod].[dbo].[B_BP_Customer]
FROM 'c:Baanjedox_dailyjdcom4401.txt'
WITH
[code]....
The error message is:
[size=1Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (BP_Country).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".size=1]..The have checked and re-checked the BP_Country field ( the 1st field after the key) and I am not seeing any mismatches.
View 5 Replies
View Related
Mar 17, 2015
Have installed SQL Server 2008 R2 Express (includes SSMS tool) on Windows server 2008 R2 sp1 without any issues.Database created with no issues, full text catalog created via the wizard also with no issues but cannot run the process as a scheduled task of updating the catalog because the SQL agent is not available in the express version.
The full text index information is already being populated and updated by a third party application so this leaves just the catalog to be updated as and when new full text information is available.
I have a third party SQL scheduler which will run SQL scheduled tasks but requires a script to run the full text catalog update process
Is it possible to extract a script from the existing full text catalog to run the update process or how to create a script from scratch to do the same update catalog process in the third party scheduler?
View 1 Replies
View Related
Dec 10, 2014
I need to update a large table, about 55 million rows, without filling the transaction log, in the shortest time as possible. The goal is to alter the table and change the data type for Text column from VARCHAR(7900) to NVARCHAR(MAX).
Since I cannot do it with an ALTER TABLE statement (it would fill up the transaction log) I'm thinking to:
- rename column Text in Text_OLD
- add Text column of type NVARCHAR(MAX)
- copy values in batches from Text_OLD to Text
The table is defined like:
create table DATATEXT(
rID INTEGER NOT NULL,
sID INTEGER NOT NULL,
pID INTEGER NOT NULL,
cID INTEGER NOT NULL,
err TINYINT NOT NULL,
[Code] ....
I've thought about a stored procedure doing this but how to copy values in batch from Text_OLD to Text.
The code I would start with (doing just this part) is the following, but maybe there are more efficient ways to do it, or at least there's a better way to select @startSeq in the WHILE loop (avoiding to select a bunch of 100000 sequences and later selecting the max).
declare @startSeq timestamp
declare @lastSeq timestamp
select @lastSeq = MAX(sequence) from [DATATEXT] where [Text] is null
select @startSeq = MIN(Sequence) FROM [DATATEXT] where [Text]is null
BEGIN TRANSACTION T1
WHILE @startSeq < @lastSeq
[Code] ....
View 1 Replies
View Related
Apr 25, 2007
On Sql Server 2005 Standard if I insert a new row into SomeTable that has a full-text index:
insert SomeTable(keywords)values('this is a test')
and then query:
SELECT * FROM Message WHERE Contains(keywords, ' "test" ');
I get the expected rows all rows that have "test" in the keyword.
On Sql Server 2005 Express new rows are not returned unless I rebuild the catalog.
Is this a known limitation of Express?
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE TABLE [dbo].[SomeTable](
[key] [int] IDENTITY(1,1) NOT NULL,
[keywords] [nvarchar](255) NOT NULL
CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON [dbo].[SomeTable] KEY INDEX [PK_SomeTable] ON [ft] WITH CHANGE_TRACKING AUTO
ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ADD ([keywords])
ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ENABLE
View 1 Replies
View Related
Sep 4, 2014
The FirstName and LastName values being passed in are in cyrillic text.
How do I add the N prefix in the update statement in order to store the text correctly.
I've tried FirstName = N@Firstname
or FirstName = '''N''' + @FirstName + '''
[dbo].[sp_UpdateDealerPeopleInfo](
@PersonId char(9),
@OriginalSID char(9),
@FirstName Varchar(50),
@LastName Varchar(50),
[code].....
View 8 Replies
View Related
Jan 10, 2002
I have an existing table called OrderHeader with a column called ID. The ID column is currently set as an INT. I want to run a script to modify that column to be an IDENTIY. I've tried the following:
ALTER TABLE OrderHeader ALTER COLUMN [ID] INT IDENTITY
but this does not work.
Any idea how to accomplish this?
Thanks!
Mike
View 1 Replies
View Related
Apr 28, 2004
I am trying to modify data in a tble using the Stored Procedure below. It doesnt work properly. I seem to only be getting one character. I think it has something to do with me using "nchar" with the variables. The value I am changing is actually a string.
Alter Procedure usp_UpdateJobName
@JobNameOld nchar, @JobNameNew nchar
As
UPDATE
JobName
SET
JobName=@JobNameNew
FROM
tblRMADATA
WHERE
tblRMADATA.JobName=@JobNameOld
View 4 Replies
View Related
Apr 28, 2004
I have a DTS package package1 which imports an excel sheet into my DataBase.ANd its saved and scheduled and running fine.Actually the excel sheet will be located daily @ c:ruceexceldata.But now we wanted to change the location of the file to c:ruce1ewexceldata
So how and where can I change my package.
Thanks.
View 2 Replies
View Related
May 5, 2004
I have a scheduled DTS package which gets the data from a text file and imports into a sql table.The table will be droped and created whenever the DTS package runs.previusly the table has 6 rows and now i wanted to add a column row_id which is having an idenity seed.So how can I modify my package so that I can get done with my requirements.
View 1 Replies
View Related
May 29, 2008
Hi,
I have a query that I am working on that involves 2 tables. The query below is working correctly and bringing back the desired results, except I want to add 1 more column of data, and I'm not exactly sure how to write it.
What I want to add is the following data.
For each row that is brought back we want to have the COUNT(*) of users who joined the website (tbluserdetails) where their tbluserdteails.date is > the tblreferemails.referDate
Effectively we are attempting to track how well the "tell a friend" via email feature works, and converts to other joined members.
Any assistance is much appreciated!!
thanks once again
mike123
SELECT CONVERT(varchar(10),referDate,112) AS referDate,
SUM ( CASE WHEN emailSendCount = 0 THEN 1 ELSE 0 END ) as '0',
SUM ( CASE WHEN emailSendCount = 1 THEN 1 ELSE 0 END ) as '1',
SUM ( CASE WHEN emailSendCount = 2 THEN 1 ELSE 0 END ) as '2',
SUM ( CASE WHEN emailSendCount = 3 THEN 1 ELSE 0 END ) as '3',
SUM ( CASE WHEN emailSendCount > 3 THEN 1 ELSE 0 END ) as '> 3',
SUM ( CASE WHEN emailSendCount > 0 THEN 1 ELSE 0 END ) as 'totalSent',
count(*) as totalRefers,
count(distinct(referUserID)) as totalUsers,
SUM ( CASE WHEN emailAddress like '%hotmail%' THEN 1 ELSE 0 END ) as 'hotmail',
SUM ( CASE WHEN emailAddress like '%hotmail.co.uk%' THEN 1 ELSE 0 END ) as 'hotmail.co.uk',
SUM ( CASE WHEN emailAddress like '%yahoo.ca%' THEN 1 ELSE 0 END ) as 'yahoo.ca',
SUM ( CASE WHEN emailAddress like '%yahoo.co.uk%' THEN 1 ELSE 0 END ) as 'yahoo.co.uk',
SUM ( CASE WHEN emailAddress like '%gmail%' THEN 1 ELSE 0 END ) as 'gmail',
SUM ( CASE WHEN emailAddress like '%aol%' THEN 1 ELSE 0 END ) as 'aol',
SUM ( CASE WHEN emailAddress like '%yahoo%' THEN 1 ELSE 0 END ) as 'yahoo',
SUM ( CASE WHEN referalTypeID = 1 THEN 1 ELSE 0 END ) as 'manual',
SUM ( CASE WHEN referalTypeID = 2 THEN 1 ELSE 0 END ) as 'auto'
FROM tblreferemails R
WHERE DateDiff(dd, referDate, GetDate()) < 5
GROUP BY CONVERT(varchar(10),referDate,112)
ORDER BY referDate DESC
CREATE TABLE [dbo].[tblUserDetails]
(
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL,
[EmailAddress] [varchar](50) NULL,
[Date] [datetime] NULL,
[Active] [tinyint] NULL
)
CREATE TABLE [dbo].[tblReferEmails](
[emailID] [int] IDENTITY(1,1) NOT NULL,
[referUserID] [int] NOT NULL,
[destinationName] [varchar](50) NULL,
[emailaddress] [varchar](50) NOT NULL,
[referDate] [datetime] NOT NULL,
[referalTypeID] [int] NULL,
[deleted] [int] NULL,
[emailSendCount] [int] NULL,
[lastDateSent] [smalldatetime] NULL
) ON [PRIMARY]
GO
View 3 Replies
View Related
Nov 19, 2007
Greetings
I have 3 tables. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType grouped by the CallDetailID and ordered by the date created
SCRIPTS
DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)
INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA055', 67, 195, Null
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 2, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 3, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 2, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '11/5/2007 12:12:34 PM'
--
--select * from @Request
Query written to achieve the requirement
UPDATE r
SET r.UniqueNo = p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallDetailID, c.CallType ORDER BY cd.EntryDt) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
WHERE r.UniqueNo IS NULL
select * from @Request
OUTPUT :
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 2
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 1
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 1
66234 Mutuk SA059 72 185 1
2232362 Buck SA055 67 195 1
EXPECTED OUTPUT: (See the last column for unique nos).
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 2
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 2
66234 Mutuk SA059 72 185 2
2232362 Buck SA055 67 195 2
How can I modify the query to achieve the expected output?
Thanks:
View 9 Replies
View Related
Feb 13, 2008
Hi our reports server has some reports on it.
we need to modify some of them. how can i get those reports on to "SQL BUSSINESS INTELLIGECSE DEVELOPMENT STUDIO".
can we get rdl file from reporting server ?
View 3 Replies
View Related
Sep 27, 2007
Can I modify 2000 DTS in 2005.I mean to say add some functionality to the existing DTS.
View 1 Replies
View Related
Oct 16, 2007
I need help with modifying this procedure to join JobTypeGallery, Remodel on JobTypeGallery.TypeID and Remodel.TypeID.
I would like for it the procedure to not allow deleting a record from JobTypeGallery if there are any records in Remodel Table that is associated with JobTypeGallery. Can someone please help me modify this stored procedure?
Create PROCEDURE [dbo].[spDeleteJobTypeGallery] @typeid int AS delete from jobTypeGallery where typeID = @typeid GO
View 4 Replies
View Related
Dec 6, 2004
I probably know that I don't want to do this, but I have an odd case.
I have a database with a bunch of stored procedures and functions. 128 of them reference a different database than the one they're in. For testing puposes, I need to temporarily re-point these functions and procedures to a different database.
I suspect the answer will be a resounding 'No', but I was wondering if it was possible to somehow run a query against syscomments where I could update all of those objects with the temporary database name rather than edit each and every one of them. Conceptually, it would just be an UPDATE using REPLACE. Pretty comfortable with that, but I'm always very reluctant to mess with stuff like that.
I'll probably still just do it through QA, but I was wondering if it's possible to do something like that and thought it might be an interesting topic for discussion.
Thanks,
Pete
View 2 Replies
View Related
Jul 31, 2002
Hello ,
I want to drop a table from a publication , so that i can copy some data from another server . After the copy , i want to add the article or the table back again to the publication without making any changes to the subscribers configuration .
Is it really possible on SQL 7.0 ? Right now it does not allow me to copy the data to the published table or even drop the article (table) and it says that the article is published for replication and cannot be dropped or modified .
The table is configured for transactional replication .
I will have to drop the entire publication and create all the subcribers again .
Any help in this regard please .
Sameer
View 2 Replies
View Related
Feb 1, 2001
I've learned to create DTS packages, saving them in a SQL database. But how can one modify a saved DTS package? And how does one delete a saved DTS package?
thanks,
Boog
View 2 Replies
View Related
Jan 3, 2000
I have a trigger that emails me each time an order is modified by a manager. I can't recall how to find out which user is performing the modification. Is there a global variable such as @@username?
Thanks,
-Darin.
View 2 Replies
View Related
Dec 19, 2002
I have some rather large TSQL scripts I'd like to schedule as jobs. Unfortunately, the SysJobSteps table in the MSDB Database is limited to 3200 characters while I need it to be 5000. Does anyone know if it's possible to increase the size of this to allow for larger TSQL scripts?
Thanks!
View 3 Replies
View Related
May 2, 2004
When I try to delete/modify a job on my sqlserver the following error occurs:
error 14274: Cannot Add, update or delete a job that originated from a n MSX server.
What does that mean and how can I solve this ?
View 1 Replies
View Related
May 23, 2008
HI Gurus,
I am tring to add table to alraedy existing DTS pacakge to refresh data from other server.
I dragged items from tool list (SQL SERVER Entripice manager)
Ther ia m not able to connect those two icons(one source and other destination).
How can i proced to next step.
Thanks,
ServerTeam
View 3 Replies
View Related
Jun 18, 2008
I have two tables, a data table (MainTable) that contains a user initials and other empty rows and then i have a user table.
I also have an inner join view with the initials from both tables as the joining field.
I want the users to be able to modify the data table (MainTable) but also display the users name field.
here is the sql of the view:
SELECT dbo.MainTable.DateCreated, dbo.InitialsListing.Initials, dbo.MainTable.CustomerNumber, dbo.InitialsListing.AdvocateName,
dbo.MainTable.Supervisor, dbo.MainTable.Complete, dbo.MainTable.FirstNotify, dbo.MainTable.SecondNotify, dbo.MainTable.ThirdNotify,
dbo.MainTable.AdvocateInitials
FROM dbo.InitialsListing INNER JOIN
dbo.MainTable ON dbo.InitialsListing.Initials = dbo.MainTable.AdvocateInitials
View 3 Replies
View Related
Jan 21, 2014
I have a field called "assescode9" in a table called "Assessment" which has the length of "6" and I want to change this length to 7 or 8 in SQL. I tried right click on the field and modify but when I want to save it says you can not save the changes because it requires a Drop table and recreate or you do not have an option enabled to recreate the table, how do I do this?
I have never done dropping a table and recreate it?
View 3 Replies
View Related
