Transact SQL :: Update Based On Results From Select
Jul 24, 2015
Update statement based on the results from this SELECT:
SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,
RELSP_STATUS_CD
FROM RELEASE_QUEUE WITH (NOLOCK)
[Code] ....
I need to update all records where the
RELEASE_STEPS.RELSP_STATUS_CD = 'SKPD'TORELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'
I imagine that the UPDATE statement will be something like:
UPDATE RELEASE_STEPS SET dbo.RELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'
WHERE (
SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,
[Code] .....
View 4 Replies
ADVERTISEMENT
Jul 14, 2015
My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum PolicyStartdate
123G 01/01/2012
456D 02/16/2012
789A 01/21/2012
163J 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates
Ex: policynum ='456D' It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I need t-sql code to get above results.
View 12 Replies
View Related
Feb 6, 2008
Hello,
I'm trying to update FOR_SORTING to 1, for all instances of a RESPONSE_ID when QUESTION_ID = YESNO and RESPONSE = Yes.
So, for below I'm trying to update FOR_SORTING to 1, for RESPONSE_ID that has Question_ID that is equal to YESNO and Response equals Yes.
No. RESPONSE_ID QUESTION_ID RESPONSE FOR_SORTING
1. 1 RATING Incredible
2. 1 YESNO Yes 1
3. 2 RATING Incredible
4. 2 YESNO No
The Table is called LSN_RESPONSE_DETAILS
I tried the following, but it only updates the one row.
UPDATE LSN_RESPONSE_DETAIL
SET FOR_SORTING = '1'
WHERE QUESTION_ID = 'YESNO' and 'RESPONSE = 'Yes'
Any idea's?
View 3 Replies
View Related
May 21, 2015
I have a data structure like this
UID , Name, amount, start date End Date
1 A 10 2015-05-01 00:00:00 2015-05-01 23:59:59
2 A 10 2015-05-02 00:00:00 2015-05-02 23:59:59
3 A 10 2015-05-03 00:00:00 2015-05-03 23:59:59
4 A 10 2015-05-04 00:00:00 2015-05-04 23:59:59
5 B 10 2015-05-05 00:00:00 2015-05-05 23:59:59
[code]...
View 5 Replies
View Related
Oct 5, 2015
In t-sql 2012, I have the following sql that I would like the following to occur:
1. commit or rollback a transaction based upon the results of a calculation listed below,
2. I would like to have a message appear if the commit was successful or the rollback needed to occur. I basically want a way to be able to tell from messages if a rollback occurred or a commit happened.
DECLARE @TransactionName varchar(20) = 'Transaction1';
@STARTLOCKERCNT INT = 0, @LOCKDIFCNT INT = 0, @ENDLOCKERCNT INT = 0
DECLARE @lockmap TABLE (lockID int NOT NULL PRIMARY KEY,
schoolID int NOT NULL,
UNIQUE(schoolID,lockID)
)
[Code] ....
Thus can you modify the sql I just listed above so that I meet the goals that I just listed above?
View 5 Replies
View Related
Oct 13, 2015
I've got a select as follows:
select computer, count(*) as MissedCount from WInUpdates_Neededreq
WHERE LoggedDate BETWEEN DATEADD (DAY, - 5, GETDATE()) AND GETDATE() and LastReportTime !< DATEADD (DAY, -5, GETDATE())
group by computer
I need to make a join onto another table but don't want to lose the coutn(*) as MissedCount.
How can I join to another table and still keep the count form the original table. I want ot join to tblogons.workstationname and return computer from the original query...
View 16 Replies
View Related
Apr 6, 2008
Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:
Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3
Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road
I'd like the results to be like this:
Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30
Anyone? Thanks in advance!
View 3 Replies
View Related
Sep 7, 2015
I have table A with colums ID and Product. I have table B with ID (same as table A) and revenue of product.
I need to update the field Product of table A with the revenue of table B.
I'm confuse with the update joining both tables. I tried this, but obviously has an error:
Update A set Product=B.Revenue where A.ID=B.ID
View 6 Replies
View Related
Jul 24, 2015
I'm trying to delete the selected data from a table colum from a select statement.
This is the select statement
SELECT RFC822 FROM SQLGOLDMINE.DBO.MAILBOX MB WHERE ((MB.CREATEON >= '2014-07-24' AND MB.CREATEON <= '2015-07-24') OR (MB.MAILDATE >= '2014-07-24' AND MB.MAILDATE <= '2015-07-24')) AND (MB.MAILREF LIKE '%auction notification
& invitation%')
What the delete statement would look like. I've tried replacing Select with delete from but get a sytax error.
View 9 Replies
View Related
Jul 25, 2015
Below is the resultset I got using the following SQL statement
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY create_date DESC) AS RowNum
,ID
,create_date
,NULL AS end_date
FROM dbo.Table_1
Resultset:
RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 NULL
[Code] ....
Now, I want to update the end_date column with the create_date's values for the next row_number. Desired output is shown below:
RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 2015-02-18
[Code] ....
View 4 Replies
View Related
Oct 27, 2015
I'm trying to Update a column table based on values from another table but I need these values are random.My query looks like this and doesn´t work
DECLARE @Rowini int,
DECLARE @lastrow int
SET @Rowini = 1
SET @Lastrow = 80000
[code]...
View 8 Replies
View Related
Sep 15, 2015
I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
job_cd permit_nbr reference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1 991 100003
ABC1 992 100004
ABC1 993 100005
ABC2 880 100006
ABC2 881 100007
ABC2 881 100007
ABC2 882 100008
ABC2 882 100008
View 3 Replies
View Related
Apr 2, 2015
This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.
Requirements:
1.
If
'Channel'
is
not
equal to "Omnibus"
where
the 'Trans Description'is
equal to "Purchase"
and
"Redemption"
for
one purchase and
one redemption that match on 'System'
,
'Account TA Number'
,
'Product Name'
,
'Settled Date'
,
and
where
the 'Trade Amount'
of the purchase and
redemption is
within 5%,
then
display those set of records.
2.
If
deemed wash trades,
allow user to update the purchase and
redemption pair 'Trans Description'
from
"Purchase"
to "Exchange In"
and
'Trans Description'
from
"Redemption"
with
"Exchange out"
System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description Trade Amount
SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption $ 25,68,458.15
[Code] .....
View 36 Replies
View Related
May 1, 2015
SQL Ver: 2008 (not r2)
Problem: The following code returns correct results when moving variable declarations and update statement outside a stored procedure, but fails to return a value other than zero for the "COMPANY TOTAL" records. The "DEPT TOTAL" result works fine both in and outside the sp.This may have to do with handling NULL values since I was getting warning message earlier involving a value being eliminated by an aggregate function involving a NULL. I only got this message when running inside the sp, not when running standalone. I wrapped the values inside the SUM functions with an ISNULL, and now return a zero rather than NULL for the "COMPANY TOTAL" records when running inside SP.All variable values are correct when running.
SQL CODE:
DECLARE
@WIPMonthCurrent date = (SELECT TOP 1 WIPMonth FROM budxcWIPMonths WHERE ActiveWIPPeriod = 'Y')
select @WIPMonthCurrent as WIPMonthCurrent
[code]....
View 10 Replies
View Related
Jul 9, 2015
I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:
SELECT tblGRNItem.GRNID
FROM tblGRNItem
INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp
WHERE tblGRNItem.DateCreated BETWEEN
(SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT
[code]....
View 6 Replies
View Related
Dec 4, 2015
I'm trying to fill a temp table whose columns are the same as another table plus it has one more column. The temp table's contents are those rows in the other table that meet a particular condition plus another column that is the name of the table that is the source for the rows being added.
Example: 'permTable' has col1 and col2. The data in these two rows plus the name of the table from which it came ('permTable' in this example) are to be added to #temp.
Data in permTable
col1 col2
11, 12
21, 22
Data in #temp after permTable's filtered contents have been added
TableName, col1 col2
permTable, 11, 12
permTable, 21, 22
What is the syntax for an insert like this?
View 2 Replies
View Related
Jun 21, 2007
the following criteria.
i have the selection all done but am trying to figure out how to do the following:
if column4 < 0 then add column4 to column3, move 0 to column4;
if column3 < 0 then add column3 to column2, move 0 to column3;
if column2 < 0 then add column2 to column1, move 0 to column2;
add column3 to column4;
move column2 to column3;
move column1 to column2;
if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;
these are all numeric data types.
View 7 Replies
View Related
Oct 22, 2014
I have the followinf select statement..
SELECT - 1 AS OrganisationID, '--Please Select--' AS OrganisationName
UNION ALL
SELECT OrganisationID, OrganisationName
FROM tblOrganisation
ORDER BY OrganisationName
Results
OrganisationID OrganisationName
22 Animal
15 Birds
-1 --Please Select--
40 Reptiles
36 Snakes
I want the results to be:
OrganisationID OrganisationName
-1 --Please Select--
22 Animal
15 Birds
40 Reptiles
36 Snakes
How can I update my SQL select statement to yield these results..
View 6 Replies
View Related
Jul 9, 2015
I am trying to fetch records from excel sheet using Select Query but I am getting the error message saying
"Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."
Here is my Query,
sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go
reconfigure
SELECT *
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:M2MworkedworkedBOS.xlsx;HDR=YES', 'select * from [Sheet1$]') AS A;
View 5 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
Sep 6, 2006
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure
declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)
'@newpin being the result from first query
Any help appreciated
View 4 Replies
View Related
Jan 3, 2006
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):
SELECT FirstName, LastNameFROM MasterUNION ALLSELECT FirstName, LastNameFROM CustomORDER BY LastName, FirstName
I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated!
Thanks,
Randy
View 5 Replies
View Related
Nov 18, 2015
How I can get the desired result using query. I don't want any
'@' variable in the result.
DECLARE @ST AS varchar(10)='AA'
DECLARE @desc AS int=8
DECLARE @STID AS int=4
DECLARE @PP AS int=63
DECLARE @SS AS int=22
/* Desired Result */
Update #RT Set ST='AA', desc=8, STID=4 Where PP=63 and SS=22
View 9 Replies
View Related
May 6, 2015
I have a stored procedure in which I'll select some rows based on a condition and I need to update the status of those rows within the same stored procedure.For e.g.
Create Procedure [dbo].[myProcedure] As BEGIN BEGIN TRAN T1 SET NOCOUNT ON SELECT TOP 5 * INTO #TempTable FROM myTable WHERE ENABLED = 1 AND FetchDate<=GetDate(); UPDATE myTable SET [Status] = 'Locked' From myTable Inner Join on #TempTable myTable.id = #TempTable.id; SELECT * FROM #TempTable; DROP Table #TempTable; COMMIT TRAN T1 END
The Stored Procedure works fine when I debug in SQL. I'm accessing the StoredProcedure through C# like this.
private ProcessData[] ReadFromDb(string StoredProcedure, SqlConnection Connection) { List<ProcessData> Data = new List<ProcessData>(); SqlCommand Command = new SqlCommand(StoredProcedure, Connection); Command.CommandType = System.Data.CommandType.StoredProcedure; try { Command.CommandTimeout = CONNECTION_TIMEOUT;
[code]....
The problem is I'm getting the required rows in C# but the update query in stored procedure is not working.
View 3 Replies
View Related
Dec 2, 2015
I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.
Update TableName
SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)'
Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range
Note: ROW is Partition By ColumnA,ColumnB
TableName: PromotionList
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 0 NULL
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 0 NULL
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
Expected outcome after performing update on the table
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 1 Overlap with row ID 4
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 Overlap with row ID 5
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
View 4 Replies
View Related
May 15, 2015
I have a stored proc which evaluates a table of 'tasks' to return the task with the highest priority for a given user - at the same time, once that task has been found - I want to allocate that user to the task.
I have created a stored proc that works...however I'm sure this requirement /pattern is common & I would like some best practice for this pattern of update and select within a transaction.
Here is some sample data:
use tempdb;
go
if OBJECT_ID('Tasks', 'U') is not null
drop table tasks;
go
create table tasks (
TaskId int identity primary key,
[Code] ....
And here's what my current stored proc looks like;
if OBJECT_ID('pGetNextTask', 'P') is not null
drop proc pGetNextTask;
go
create proc pGetNextTask (
@UserID char(10),
@TaskID int output
)
[Code] ....
View 3 Replies
View Related
Nov 17, 2015
I have a daily record table (has field Date1, field2Update) and have another table(has field Date2, Flag (value: 0 and non 0 values)
I want to update table1 and set field2Update as somevalue where table2.Flag <> 0
View 4 Replies
View Related
Jul 10, 2015
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
View 6 Replies
View Related
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
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
Apr 26, 2015
I have two tables A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.
View 5 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Jun 19, 2008
Here's one thats had me and a coworker puzzled. Hopefully it's something simple:
TABLE A: (log of messages)
userid, int
date, datetime
message, varchar(50)
Table A Data:
1, 6/18/2008 @ 2:32:41, This is my message
1, 6/18/2008 @ 2:31:02, This is my message
1, 6/17/2008 @ 7:34:26, This is another message
2, 6/18/2008 @ 2:32:41, This is not his message
2, 6/16/2008 @ 11:21:32, This is my message
TABLE B: (List of users)
userid, int
name, varchar(100)
Table B Data:
1, John
2, Mike
I want to extract the most recent message logged per user, i.e.:
name | date | message
--------|---------------------|-------------------------
John | 6/18/2008 @ 2:32:41 | This is my message
Mike | 6/18/2008 @ 2:32:41 | This is not his message.
I have been unable to come up with a query that can return just the one value. I've tried variants of:
SELECT DISTINCT b.name, a.date, a.message
FROM a INNER JOIN
b ON a.userid = b.userid
including sub-queries and even played with the visual diagrams trying to design this in Enterprise Manager but none of the combinations I tried work. Is there an easy way to do this via a query? I don't have experience with stored procedures. Would that be necessary?
TIA
View 4 Replies
View Related
