Need To Convert This Select Into An Update
Sep 18, 2001
In answering this question you could just tell me what you think would work rather than trying to test it.
The following query works correctly and returns rows with pairs of values repid and repid2. I need loop through a large table and update *its* repid2 with rm_repid2 for any rows where its repid value equals rm_repid but only for the pairs that result in the below query
SELECT
rm.repid AS rm_repid
,rm.repid2 AS rm_repid2
,rm.id2contact
,r.repid AS r_repid
,r.prim AS r_prim
FROM sfrep r JOIN sfrepmst rm ON r.repid=rm.repid2
WHERE rm.repid IN
(
'TEST01'
,'TEST02'
)
Returns
rm_repid rm_repid2 id2contact r_repid
-------- --------- ---------------------------------------- -------
TEST01 NEW01 John Smith NEW01
TEST02 NEW02 Ken Roberts NEW02
TIA
Doug
View 1 Replies
ADVERTISEMENT
Apr 15, 2008
Hello!
I am trying to write an update t-SQL statement from the following select statement:
SELECT EduNextContacts.ssn
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code
GROUP BY EduNextContacts.ssn
HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) +
SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2)
I tried many versions of writing my update statement with no luck. My most recent version is as follows:
UPDATE EduNextContacts
SET EduNextContacts.overLMLimit = 'Y'
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code
WHERE EduNextContacts.ssn = (SELECT DISTINCT EduContactsAuditChanges.ssn
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code
GROUP BY EduContactsAuditChanges.ssn
HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) +
SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2))
And gives the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Can anyone help shed some light on how to make an update statement from my SELECT query above?
Thanks in advance,
Harry
View 3 Replies
View Related
Jun 10, 2015
Consider the below script
CREATE TABLE #TEMP(Id int,CreatedBy varchar(30),ModfiedBy varchar(30))
CREATE TABLE #TEMP2 (ID int,SearchedBy varchar(30))
INSERT INTO #TEMP VALUES(1,'James',NULL)
INSERT INTO #TEMP VALUES(1,'James','George')
INSERT INTO #TEMP VALUES(1,'James','Vikas')
INSERT INTO #TEMP2(ID) VALUES(1)
INSERT INTO #TEMP2(ID) VALUES(1)
INSERT INTO #TEMP2(ID) VALUES(1)
Now i want to get the result as
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
SELECT CASE WHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
But when i convert this select to update, i am missing something...
My update is
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE #TEMP2
SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
WHERE #TEMP2.ID=CTE.ID
Only the first record gets updated...
View 9 Replies
View Related
Nov 29, 2007
Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
Thanks
View 1 Replies
View Related
Oct 12, 2004
Hello,
In DB2, we can do FOR UPDATE selection, in order to lock the records until the transaction is completed. e.g.
select field1, field2, field3
from table
FOR UPDATE;
How can I perform the equivalent feature in SQL Server?
Thanks!
View 4 Replies
View Related
Jul 6, 2014
I have a table with all varchar data for all different fields for money and int etc. I want to do a calcuation in one field with the rule as below:
UPDATE [dbo].[tblPayments]
SET [PreInjuryWage]=
CASE
WHEN [WeekNo]<14
THEN ([MaxWorkCover]+ [WeeklyEarnings] )/0.95
ELSE ([MaxWorkCover] + 0.80 * [WeeklyEarnings] ) /0.80
END
I tried this command after many other ways of tries
UPDATE [dbo].[tblPayments1]
SET [PreInjuryWage]=
CASE
WHEN [WeekNo]<14
THEN CAST ( (cast([MaxWorkCover] as money )+ cast([WeeklyEarnings] as money ))/0.95 AS Varchar(10) )
ELSE CAST ( (convert(money, [MaxWorkCover] + (0.80 * convert(money, [WeeklyEarnings] )) )/0.80 ) as varchar(10))
END
The structure of the table ( its a legacy table from 2000 i beleive)
CREATE TABLE [dbo].[tblPayments](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[ClaimID] [int] NOT NULL,
[WeekNo] [smallint] NULL,
[code]....
Arithmetic overflow error converting numeric to data type varchar.
View 6 Replies
View Related
Jun 9, 2006
I am pulling data from one table to another, a column in table 1 is "allow function" the corresponding column in table 2 is "block function". So when the value in table 1 is "0" it needs to be "1" in table 2 and when it is "1" in table 1 it needs to be "0" in table 2.
This select/insert is being done in a stored proc and I am at a loss on how to efficiently handle this conversion.
Any suggestions?
Thanks
View 1 Replies
View Related
Apr 13, 2007
Hi Guys
I need your help again, I am try to update several columns and the data type is 'money'.
Below is the code I have used:
UPDATE CAT_Products SET
UnitCost ='10.00',UnitCost2 = '10.00',UnitCost3 = '10.00',UnitCost4 = '10.00',UnitCost5 = '10.00',UnitCost6 = '10.00'
WHERE ProductCode = '0008'
But it will not update, instead I get this error:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>[Error] Script lines: 1-9 -------------------------- Disallowed implicit conversion from data type varchar to data type money, table 'dbo.CAT_Products', column 'UnitCost'. Use the CONVERT function to run this query.
More exceptions ... Disallowed implicit conversion from data type varchar to data type money, table '.dbo.CAT_Products', column 'UnitCost2'. Use the CONVERT function to run this query.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The error message indicates that I need to use the convert function. But the columns data type is set at 'money' not 'varcher' . So do I need to convert data type to 'varcher' in order to update and convert back to data type 'money' when update complete? Or do I need to indicate in the update statement that data type is already 'money'? I am not sure how I would either.
Thanks
View 2 Replies
View Related
Apr 7, 2004
I am trying to convert code I have working for access to work with SQL.
fldName, fldEmail, ID are the names in the database. recNum does have the value of the record that I want to edit. Here is the error I am getting.
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '?'.
And here is the stack trace (which I don’t know how to read except for the line the error is on)
[SqlException: Line 1: Incorrect syntax near '?'.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
goodellweb.adm_contact.editNow_Click(Object sender, EventArgs e) in C:Inetpubwwwrootwebrootgoodellwebadmadm_contacts.aspx.vb:306
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1315
here is my code.
Dim editSQL As String = "Update tbEmail Set fldName=?, fldEmail=? Where ID=?"
Dim SqlConn As New SqlConnection(ConnStr)
Dim Cmd As New SqlCommand(editSQL, SqlConn)
Cmd.Parameters.Add(New SqlParameter("@fldName", nameEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@fldEmail", emailEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@recNum", recNum))
SqlConn.Open()
Try
Cmd.ExecuteNonQuery()
Finally
SqlConn.Close()
End Try
Response.Write("recNum " & recNum & " <br>")
Thanks
Michael
View 4 Replies
View Related
Feb 15, 2008
I have this update working in an Oracle database and I need to make it run on SQL Server.
UPDATE table3 C
SET C.column1 = 'A'
WHERE (C.column2, C.column3) IN (SELECT B.column2, B.column3 FROM table1 A, table2 B WHERE A.column2 = B.column2 AND A.column3 = B.column3 AND B.column4 = 1)
Help please.
Thanx!
View 1 Replies
View Related
Sep 26, 2007
select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)
From above query I get
mmddyyyyhhmm
but it' s yyyy and hour can not be separated
04/12/200702:05
How can I separated the year and hour ?
Thanks
Daniel
View 2 Replies
View Related
May 18, 2007
The problem...
A new value is inserted automatically with (convert(smalldatetime,getdate()103)) to a smalldatetime field (date) into a new row in the db
Existing data needs to be updated via an html form in the format ”dd/mm/yyyy” - I get this when I try: “Error The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value”
Obviously the db field doesn’t like trying to update a smalldatetime field with a char type value. As the date field needs to use some SQL later with DATEADD, I can’t change the date field to char. (The date feild accepts ”dd/mm/yyyy” if I use Enterprise Manager and type it strait in but not via the update on a web page?)
The question...
How do I change the update SQL to convert from Char to Smalltimedate to accept dd/mm/yyyy?
( I am using MS-SQL and ASP3 JS and new(ish) to MS-SQL)
View 8 Replies
View Related
Oct 31, 2006
The data I have has a 1 in for each Yes answer and a 2 for each no answer. I just want the select statement to show the word yes when there's a 1 and the word no when there's a 2. I don't need to update or change the database. Could anyone lead me in the right direction here? Thanks
View 4 Replies
View Related
Jul 23, 2005
I am trying to assign @sql variable to @total, where @sql is a sqlstatement stored on the database, however what I am getting is itsstring value and not its calcuation. Could anybody help?DECLARE my_cursor CURSOR FORSELECT sqlstatement from Sn_SalesReportdeclare @sql varchar(255), @total varchar(20)OPEN my_cursorFETCH NEXT FROM my_cursor INTO @sql-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGINSET @total = (@sql)print @total-- This is executed as long as the previous fetch succeeds.FETCH NEXT FROM my_cursorINTO @sqlENDCLOSE my_cursorDEALLOCATE my_cursorI tried this SET @total = EXEC (@sql), but no successRod
View 9 Replies
View Related
May 21, 2008
I have the following code:
INSERT INTO Reports_PI_Recent
SELECT TOP(13)* FROM Reports_PI
ORDER BY RecordKey desc
problem is that the data I am trying to insert is of the type nvarchar. eg: 06.50
I need it to be converted to type decimal (or float) before it is inserted in the new table.
Is there a way to do this within the SELECT TOP expression?
View 1 Replies
View Related
May 22, 2008
I have the following code:
INSERT INTO Reports_PI_Recent
SELECT TOP(13)* FROM Reports_PI
ORDER BY RecordKey desc
problem is that the data I am trying to insert is of the type nvarchar. eg: '06.50'
I need it to be converted to type decimal (or float) before it is inserted in the new table.
Is there a way to do this within the SELECT TOP expression?
View 6 Replies
View Related
Jan 17, 2007
I need to convert a date like 08/1/2009 to 0809
I also need to show currency as 100.00 and not 100.0000
How can I do these in a select statement?
SELECT CONVERT(Varchar(20),ExpirationDate,10) AS ExpirationDate, Amount FROM tblPayment
I appreciate any help!
View 5 Replies
View Related
Mar 18, 2015
how to convert float to timestamp in single select query..for exp. i have float as 1.251152515236 ,i want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00
View 6 Replies
View Related
Sep 27, 2007
Hi all
With this query
select convert(varchar(16), getdate(), 101)+' '+LEFT(convert(varchar, getdate(), 108),5)
I get
mmddyyyy hh:mm
How can I change my query to get
mmddyyyy hh:mm but no 0 when month from 1 to 9
example can not be: 02/12/2007 03:34
but should be: 2/12/2007 03:34
Don't know why excel not accept month with 0 from 1 to 9
Thks
Daniel
View 5 Replies
View Related
Oct 14, 2015
I have a table that holds full names but it's lastname, firstname.I need to select and convert to firstname lastname and remove the comma.
View 7 Replies
View Related
Jun 19, 2014
I want to convert xml data with select query and insert into table.
Xml sample like this :
<ROOT>
<ROW>
<NAME>JHON</NAME>
<ADDRESS>
<ADDRESS1>
<CITY>LKO</CITY>
<STATE>UP</STATE>
[code]....
And data should be like this with select query :
NAME ADDRESS1CITY ADDRESS1STATE ADDRESS2CITY ADDRESS2STATE
JHON LKO UP DLI DELHI
YASH AAA HYR NULL NULL
I want simple query in form of above format.
View 8 Replies
View Related
Jul 23, 2005
I have a table of zip codes, some with Canadian zips. I'd like to takea zip code and search for nearby zips. For example:Dim theZip As Integer = textbox1.text....Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10)....Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 10)SELECT * from ZIPCODES where Cast(zip_code as Integer) BETWEEN @lowzipAND @highzipProblem is the letters in the Canadian records cannot be cast asintegers for this process. I get this error:Syntax error converting the varchar value '53151 1' to a column of datatype int.Is there a SQL query that can exclude if no cast can be made to anInteger?Thanks!Anton
View 5 Replies
View Related
Sep 26, 2007
With this clause "select convert(varchar(16), getdate(), 101)" I can get mmddyyyy but
How can I get time such as mmddyyyyhhmm
Thanks
Daniel
View 9 Replies
View Related
May 21, 2007
Hi,
I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.
Can anyone help me please?
Thx
View 6 Replies
View Related
Mar 18, 2015
How to convert float to timestamp in single select query
E.g., i have float as 1.251152515236 ,
I want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00
View 1 Replies
View Related
Apr 24, 2007
Hello everyone,
I have the following sql query which basically gives me 10 ramdon records in a table, what i want to do is update those records selected. i.e update the select query.I want to upddate a field in the 10 records returned by the query below. Thank you.
SELECT TOP (10) *FROM tblSiebelActivity1000WHERE (DATEPART(mm, dteActivityDueRT) = MONTH(GETDATE())) AND (DATEPART(dd, dteActivityDueRT) = DAY(GETDATE())) AND (txtServiceRegion = 'nj02')ORDER BY NEWID(), txtActivityNumber
View 6 Replies
View Related
Jul 3, 2006
I have two tables. I need to put the first character in column2 table 2 into column2 table 1 where there id's are the same. The table where the information is coming from(table2) is an int datatype and the table where it is going to(table1) is a char datatype. Any help is appreciated, thanks in advance.
View 1 Replies
View Related
Mar 4, 2005
I have a sales table that has a customer_number, salesman_number and sales data (like item, quantity, etc), there is a customer table that has the customer_number, name, address, etc. and salesman_number, with the passign of time salesmen come and go and the customer service department is in charge of modifying the customer table and assign the new salesman number to them. All the previous sales have to be reasigned to this new salesman even though it wasnt him the one that sold the items (I know, I know, thats the way the company requieres it, this is an old system). Now to my question:
I have this select query that gives me the rows from table sales that have to be modified:
SELECT C.sales_rep,S.salesrep,S.*
FROMSALES AS S
INNER JOIN customer AS C
ONC.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = S.site_code AND
C.cust_no = S.cust_noAND
C.cust_sffx = S.cust_sffxAND
C.division = S.divisionAND
C.sales_rep != S.salesrep
WHERES.month = 2AND
S.year = 2005
ORDER BYC.sales_rep
I just want to know if my approach to convert this query from an update to a select is ok:
UPDATE SALES AS S
SET S.salesrep = C.sales_rep
INNER JOIN customer AS C
ONC.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = S.site_code AND
C.cust_no = S.cust_noAND
C.cust_sffx = S.cust_sffxAND
C.division = S.divisionAND
C.sales_rep != S.salesrep
WHERES.month = 1AND
S.year = 2004
Thanks for your help
Luis Torres
View 6 Replies
View Related
Jan 25, 2007
Hi there.
I have two databases, one called PWALive and one called Portal-Live.
what I want to do is create an extract from Portal-Live of e-mail addresses and then use this date to update a table in PWALive.
Here are the relevant tables
Portal-Live
tblMUD(MUD_ID, MUD_clock, MUD_email ... )
PWALive
employee(employee_number, email_address ... )
Relationship: MUD_clock = employee_number
My sql statement for the extract from Portal-Live.tblMUD looks like this:
SELECTMUD_clock,
MUD_email
FROM tblMUD
WHEREMUD_email IS NOT NULL
AND MUD_clock IS NOT NULL
I want to then update the correct employee in PWALive.employee:
UPDATE employee
SET ... --Not entirely sure what to do here!
WHERE employee_number = MUD_clock
How can I do this in one sql sequence/is it possible?
As I understand it I can use CONNECT TO but Icant get that to work, let alone know how to link the update with the first sql statement.
Big thank you in advance
-GeorgeV
View 14 Replies
View Related
Oct 25, 2007
Hi
how can you update many fields from a select ? :
UPDATE table1 (a1,b1,c1) SELECT (a2,b2,c2) FROM table2 WHERE table1.id = table2.id
for MS SQL 2000/25000
thank you for helping
View 3 Replies
View Related
May 19, 2008
I have a select statement
SELECT ORDHFILE.OABL, ORDTFILE.ODLOTSEQ, ORDHFILE.OACUSTPO, ORDHFILE.OABLDATE, ORDHFILE.OAREGN, ORDHFILE.OASHIPDTMM, ORDHFILE.OASHIPDTDD, ORDHFILE.OASHIPDTYY, ORDHFILE.OASHIPVIA, ORDHFILE.OAFOB, ORDHFILE.OAATTN, CUSTFIL2.COBILLNUM, CUSTFIL2.COCNTRY, ORDHFILE.OASFNAME, ORDHFILE.OASFAD1, ORDHFILE.OASFAD2, ORDHFILE.OASFCITY, ORDHFILE.OASFST, ORDHFILE.OASFZIP, ORDHFILE.OASFZIP2, ORDHFILE.OASTNAME, ORDHFILE.OASTAD1, ORDHFILE.OASTAD2, ORDHFILE.OASTCITY, ORDHFILE.OASTST, ORDHFILE.OASTZIP, ORDHFILE.OADESC1, ORDHFILE.OAOPID, ORDTFILE.ODPNUM, PRODFIL2.PUPNAME1, PRODFIL2.PUPNAME2, ORDTFILE.ODQORD, ORDTFILE.ODMEAS, ORDTFILE.ODUM, ORDTFILE.ODCSC, ORDREM.OCCPREM1, ORDREM.OCCPREM2, ORDREM.OCCPREM3, ORDREM.OCCPREM4, ORDREM.OCCPREM5, ORDREM.OCCPREM6, ORDREM.OCCPREM7, ORDREM.OCCPREM8, ORDREM.OCCPREM9, ORDREM.OCCPREM10, ORDREM.OCCPREM11, ORDREM.OCCPREM12
FROM B108FFCC.CHEMPAXSNF.CUSTFIL2 CUSTFIL2, B108FFCC.CHEMPAXSNF.ORDHFILE ORDHFILE, B108FFCC.CHEMPAXSNF.ORDTFILE ORDTFILE, B108FFCC.CHEMPAXSNF.PRODFIL2 PRODFIL2, B108FFCC.CHEMPAXSNF.ORDREM ORDREM
WHERE (((ORDHFILE.OAWHS)='19') AND ((CUSTFIL2.COCNUM)=(ORDHFILE.OACNUM)) AND ((ORDTFILE.ODBL)=(ORDHFILE.OABL)) AND ((PRODFIL2.PUPNUM)=(ORDTFILE.ODPNUM)) AND ((ORDREM.OCBL)=(ORDHFILE.OABL)) AND ((ORDREM.OCPNUM)=(ORDTFILE.ODPNUM)) AND ((ORDREM.OCMEAS)=(ORDTFILE.ODMEAS)))
ORDER BY ORDHFILE.OABL, ORDTFILE.ODLOTSEQ
It works fine. However the data is being duplicated every 15 minutes instead of updating the changes. I am getting the same information over and over again.
I'm using DTS SQL Server Enterprise Manager.
View 6 Replies
View Related
Jun 29, 2006
Is it possible to update an entire column with data from another table? I have allot of information that is date sensative and would rather just update one column rather than havnig to change the date all the time. I have started this string and am stuck about what tp put after the SET string
UPDATE scf_yfeesycom
SET netrealpl =
SELECT netrealpl
FROM scf_pandl
Where date > 2006-01-31
View 2 Replies
View Related
Mar 25, 2008
I have a huge table with 407 columns and around 90 million records.
I need to run an update on 10 columns..
updates as case, trimming, replace.....
What do you guys suggest? Should I run select into or run an update query that runs all night.?
Thanks!
View 5 Replies
View Related
