SQL Server 2012 :: Sub-Query For Update Statement
Feb 14, 2014
I am trying to Write an update string for individual partID's. I wrote this query below but it isn't populating the time to test.
SELECT 'UPDATE Parts SET = [TimeToTest]' + ' ' +
Convert(varchar, (select test From [dbo].[db_PartsCats] as c Join Parts As P on P.category = C.CatID Where PartID = 48727))
+ ' ' + 'WHERE PartID = ' + CONVERT(varchar, P.PartID)
From Parts As P
Where FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187
Order By categoryMy results:
Should get UPDATE Parts SET = [TimeToTest] 0.5000 WHERE PartID = 48871 But getting Nulls instead
View 5 Replies
ADVERTISEMENT
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Aug 13, 2014
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
View 1 Replies
View Related
Dec 12, 2014
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
View 5 Replies
View Related
Jun 18, 2014
I am unable to update the data using record by record below scenario.
Required output:
patient will able to Admit/Re-admit multiple times in hospital, if a patient readmitted multiple times in hospital after the first visit, first visit record will get Re-admission=0 and Index=1. This visit should cal Index_Admission of that patient. using this index_admission should calculate the 30-day readmission.
Current Output:
Calculation: From index_admission discharge date to next admit_visit date,
1) if the diff is having less than 30 days, readmission=1 and Index=0
else readmission=0 and Index=1 should be update.
For checking this every time should check using the latest index_admission discharge_date.
To get this result i written below logic, but it's updating readmission=0 and Index=1 after 30-day post discharge of using first index admission.
UPDATE Readmission
SET Index_AMI = (CASE WHEN DATEDIFF(DD, (SELECT Sub.Max_Index_Dis FROM
(SELECT Patient_ID, MAX(Discharge_Date_Time) Max_Index_Dis FROM Readmission
WHERE Index_AMI = 1 AND FPR.Patient_ID = Patient_ID GROUP BY Patient_ID) Sub)
, FPR.Admit_Date_Time) between 0 and 31 THEN 0 ELSE 1 END),
[Code] ....
Expected Result:
View 5 Replies
View Related
Nov 25, 2014
I have a simple update statement (see example below) that when runs, I expect to see the number of records updated in the Results tab. This information shows up in the Messages tab; however, what is displayed in the Results tab is (No column name) 40. From where the 40 is being generated. I have tried restarting SSMS 2012, restarting my computer, turning NOCOUNT on and off.
"UPDATE TableA
SET Supervisor = 'A123'
WHERE PersonnelNumber = 'B456'"
View 4 Replies
View Related
Mar 12, 2015
I have created a Dynamic Merge statement SCD2 Store procedure , which insert the records if no matches and if bbxkey matches from source table to destination table thne it updates old record as lateteverion 0 and insert new record with latest version 1.
I am getting below error when I ahve more than 1 bbxkey in my source table. How can I ignore this.
BBXkey is nothing but I am deriving by combining 2 columns.
Msg 8672, Level 16, State 1, Line 6
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
View 4 Replies
View Related
Oct 7, 2014
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "Fatals_CTE.t_enrollmentID" could not be bound.
Here are the table definitions:
CREATE TABLE [dbo].[Enroll](
[enrollmentID] [int] IDENTITY(10000,1) NOT NULL,
[e_PatientID] [int] NOT NULL,
[e_ProtocolNo] [varchar](30) NOT NULL,
[enrollDate] [datetime] NULL,
[enrollOK] [bit] NULL,
[Code] .....
WHERE enrollmentID = Fatals_CTE.t_enrollmentID;
(I'm trying to update an enrollment's LeaveDate to the date of his Grade 5 event. )
View 2 Replies
View Related
Oct 20, 2014
I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):
UPDATE dbo.target_table
set target_column =
case
when source_column_1= 'ABC'then 'XYZ'
when source_column_2= '123'then 'PDQ'
[Code] ....
The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.
The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.
What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.
View 9 Replies
View Related
Jul 25, 2015
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
View 2 Replies
View Related
Aug 21, 2014
How I am using a CASE statement within a WHERE clause to filter data:
CREATE PROCEDURE dbo.GetSomeStuff
@filter1 varchar(100) = '',
@filter2 varchar(100) = ''
AS
BEGIN
SELECT
[Code] .
What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.
Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?
View 5 Replies
View Related
Apr 3, 2014
I want to update one table with the value from variable using a set based approach.
so the line
>> select @List = coalesce(@list + ',','') + cast(id as varchar(10)) + cast(feetype as varchar(25))
works fine but I want to take @list and use it to update my customers table. here is the full code.
create table customers
(custid int, fee_history varchar(max))
insert into customers
(custid
, fee_history
[code]....
View 5 Replies
View Related
Aug 31, 2015
I am trying to run an update statement against a vendor's database that houses HR information. If I run a regular select statement against the database with the following query, it returns without error:
SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237When I try to convert it into an
[Code] ....
I am assuming I am receiving this error because it doesn't know where to find QUDDAT-INT? How can I fix that?
The "QUDDAT-INT" column houses the employee number. So in the case of the SELECT query above, I am testing against a specific employee number.
View 9 Replies
View Related
Sep 9, 2015
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.
Now, I was asked to log the specific record where the failure was occured.
Something like log the identity column value or primary key value which record has failed.
View 4 Replies
View Related
Jul 16, 2004
Hi,
The below query has some problem :
update (table1 inner join table2 on table1.sno = table2.sno) inner join table3 on table2.sno1 = table3.sno set table1.sno1 = 2
SQL Server 2000, throws a syntax error :
'Incorrect syntax near keyword '(' ' on executing the above statement.
Please advice 'what is the error in the above statement ? '
Thanks,
Sam
View 2 Replies
View Related
Feb 15, 2006
Hi Group!I am having a problem of using SUM under UPDATE statement. I understandthat SQL does not allow me to use SUM in UPDATE, but unfortunately, Ican not find a way to get around it. Can someone please give me someidea? Thanks a million!--Here is the criteria:Under the same PORTFOLIO_ID, if the ACCOUNT_OPENDATE is in the samemonth as the PORTFOLIO_OPENDATE, then sum the account_openamt for thesame PRODUCT, and update the CHECKING_OPENAMT, SAVINGS_OPENAMT,CD_OPENAMT, etc in table PORTFOLIO. For other accounts opened NOT inthe same month as the PORTFOLIO_OPENDATE, just ignore them.--Here are the tablescreate table portfolio(portfolio_id int,portfolio_opendate smalldatetime,checking_openamt money,savings_openamt money,cd_openamt money)insert into portfolio values(1,'2/15/2005',0,0,0)create table account(portfolio_id int,product varchar(20),account_opendate smalldatetime,account_openamt money)insert into account values(1,'checking','2/15/2005',2000)insert into account values(1,'checking','2/20/2005',3000)insert into account values(1,'savings','2/20/2005',3000)insert into account values(1,'cd','5/15/2005',5000)--Ideal Output--id portfolio_opendatechecking_openamtsavings_openamtcd_openamt1 2/15/2005500030000--Here is my query:update portfolioset checking_openamt=sum(b.account_openamt) --problem appears!from portfolio as ajoin account as bon a.id=b.idand year(a.portfolio_opendate)=year(b.account_opendate )and month(a.portfolio_opendate)=month(b.account_openda te)and product ='checking'--and product='savings'--and product='cd'Thanks again!!
View 3 Replies
View Related
Jul 20, 2005
hi myself avii am developing one appliacaion in which i am using vb 6 as front end,adodb as database library and sql sever 7 as backend.i want to update one table for which i required data from other table. andiretrive data from second table by giving some condition. when i get data,then to update first table i need to use do while loop. instead of that iwant to use select statement directly in update query.plz give me some help.following is the my queries and its out putStrSql = ""StrSql = "Select * From SalesVchMaterialDesc where TransactionID=" &txtTransactionID.text & ""rsMName.Open StrSql, Conn, adOpenKeysetDo While Not rsMName.EOFStrSql = ""StrSql = "Update StockTable Set Outward=Outward - " &rsMName("Netweight") & ",OutwardQty=OutwardQty - " & rsMName("Qty") & "Where MaterialId=" & rsMName("Material_Name") & " and VoucherDate='" &Format(rsMName("VoucherDate"), "mm/dd/yyyy") & "'RsAdd.Open StrSql, Conn, adOpenStaticrsMName.MoveNextLooprsMName.Closeout put***main querySelect * From SalesVchMaterialDesc where TransactionID=848do while not loopUpdate StockTable Set Outward=Outward - 8.06,OutwardQty=OutwardQty - 1Where MaterialId=221 and VoucherDate='04/01/2004' and SMID=0loop
View 1 Replies
View Related
Jul 29, 2015
To avoid locking/blocking, or in transaction scope, we are trying make a common practice of writing coide for update commands in our all SPs based on primary key columns in where clause. I have a following scenario...
UPDATE [dbo].[TL_CST_Locker_Issuance] SET
[isActive] = 0
WHERE
LockerIssuanceId IN (SELECT LockerIssuanceId
[Code] ...
What is the better approach and should be followed to avoid locks and gain performance or best approach.
View 7 Replies
View Related
Jun 22, 2015
I have a table A and lookup table B.
table A:
| ID | FRUIT | VEGETABLE | GOOD |
--------------------------------------------
| 1 | orange | cabbage | no |
| 1 | apple | lettuce | yes |
| 1 | kiwi | broccoli | no |
| 1 | pear | kale | yes |
table B:
| ID | FRUIT | VEGETABLE |
-------------------------------
| 1 | apple | lettuce |
| 2 | pear | kale |
If the fruit and vegetable in table A is found in table B, then set the GOOD column = yes, else no.
This is what I have so far.
update tableA
set GOOD =
(case when tableA.id = C.id then 'yes'
else 'no'
end
)
from
(select tableA.id as id
from tableA A
left join tableB B on B.fruit = A.fruit
and B.vegetable = A.vegetable) C
View 6 Replies
View Related
Dec 3, 2014
I have a database with enabling merge replication.
Then the problem is update query is taking more time.But when I disable the merge triggers then it'll update quickly.
View 1 Replies
View Related
Sep 25, 2014
I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052 Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
how to achieve this in single query?
View 4 Replies
View Related
Oct 28, 2015
I have created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
ALTER PROCEDURE [dbo].[RPT_Cost_copy]
SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM
INTO T1
FROM
(SELECT a.meu, a.mep2, SUM(a.mest) as excst
FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> ''
[code]....
View 2 Replies
View Related
May 7, 2007
I have a table which I need to obtain data from but am having a problem with select statement.
Specifically, I have a table that has multiple records for a particular hostName where the name of the host is either a shortname (say "Larry") or a long name (say "Larry's").
I need to display only the long names (NOT THE SHORT NAME records with the similar hostName).
Select winsHostName, len(winsHostName) from winsData where winsClientIPAddress IN (SELECT winsClientIPAddress from winsData Group By winsClientIPAddress Having count(winsClientIpAddress) > 1) Order by winsHostName
Which returns data
Name Length
ATVDDR 6 ATVDDR1 7
This is a s far as I can get but,
Now, I need to list all remaining table fields based on the Name with the longer length and this is where I run into trouble.
The output should read per below with the remaining table fields which I cannot seem to extract with nested select statement
Name IP Addr Location
ATVDDR1 1.1.1.1 2ndFloor
I tried adding another GROUP BY by this gets me no where because I do not need to execute another aggregate in select statement. Maybe I need to use INNER JOIN
Please advise any assistance.
View 2 Replies
View Related
Jul 23, 2005
I am trying to speed up my update statements by removing inner selectstatements.Example:update orders set shipname = (select contactName fromcustomers where customerid = orders.customerID)I read some articles which said that I should be able to use an innerjoin on the update statement like the following:update orders set shipname = (select contactName from customers wherecustomerid = orders.customerID)But every time that I run this statement I get the follwing error:Server: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'inner'.Any Help will be greatly appreciated.Thank you.
View 6 Replies
View Related
Jan 11, 2008
I've got the output from a cursor that generates the following sql statement.
Update SERVERNAME.DATABASENAME.dbo.TABLENAME set [update] = 'Y', status = NULL, completed = NULL where trigger_id = 10255
The statement generates the following error if the statement is run remotely
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'update'.
But the statement executes without error as formatted if run directly on the linked server.
Both servers are running 2000 sp 3a. The servers will be converted to 2005/2008 without patching to sp4.
View 5 Replies
View Related
May 27, 2015
I am looking to create a simple IF Else statement in a SPROC but my syantax seems to be wrong.
IF (vchDept='IT')
BEGIN
THEN 1
END
ELSE
IF vchEmail like '%@abc.com' AND vchDept<>1
BEGIN
THEN 2
END
[code]....
View 9 Replies
View Related
Oct 19, 2015
adding a if statement within my cursor.
Use master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
[code]....
The cursor should only grant access to a particular database. If the user exists within the database it should not execute the script.
View 0 Replies
View Related
Apr 16, 2015
I'm getting the following error message 'An aggregate may not appear in the set list of an UPDATE statement' What is the proper way to carry out an update on aggregates?
My code is below:
t1.TotalTest1 = coalesce(Sum(t2.AmountTest1), 0.00),
t1.TotalTest2 = coalesce(Sum(t2.AmountTest2), 0.00),
t1.TotalTest3 = coalesce(Sum(t2.AmountTest3), 0.00),
t1.TotalTest4 = coalesce(Sum(t2.AmountTest4), 0.00),
from #tbl_CHA t1
inner join
[Code] ....
View 4 Replies
View Related
Aug 5, 2014
My table structure like below.
id date value1 value2
1 5-june-2104 Yes No
1 6-june-2014 No Yes
2 5-june-2104 Yes Yes
Want to calculate yes count on any day for same id
View 5 Replies
View Related
Aug 4, 2015
I have a query that displays a bunch of fields, specifically a createdon field and closedate field.I need to find the diff in days between date1 and date2 and only disiplay those results.For example: where the NumDays = a certain value. For example, 81.I have the NumDays displaying in the query, but I don't know how to reference these values in the where clause.
SELECT
TBU.businessunitidnameAS 'BU Name',
LEADS.statecodenameAS 'Status',
LEADS.statuscodeAS 'Status Code',
LEADS.accountidnameAS 'Account Name',
[code]...
View 5 Replies
View Related
Oct 11, 2014
I wrote query to update id int(1,1) to some value but its not giving me the result. Is it not possible to update id on the table.
View 7 Replies
View Related
Nov 15, 2012
I would like to know what can be the equivalent update command in SQL server?
Code:
update Table1 set EndFlag = 1
where (c1,c2) in (select c1, MAX(c2) from Table1 group by c1)
View 2 Replies
View Related
Jan 16, 2014
Curious if I have the code below as an example and I execute this code does sql execute from top to bottom? And does the Update run and complete before the delete occurs? Or does SQL execute the update and delete in parallel?
UPDATE tbl1 SET col1 = 1
GO
DELETE FROM tbl1 where col2 = 2
View 2 Replies
View Related
