Update Select Statement To Yield Results
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
ADVERTISEMENT
Jan 9, 2008
Hi,
I have written a basic UPDATE statement to update two fields in one table using a where clause to identify the record i wish to update. i.e.
UPDATE TableName SET field1=1234, field2='' WHERE primary_key=N'1234';
When i run the statement, in the results window i get a value returned that is equal to the total number of records in that table!! i.e.
(No column name)
--------------------------
588061
but when i check the table, indeed only one record has been updated as expected (and i have confirmed this by using SELECT @@ROWCOUNT straight after)
My question is, why does it do this?? i paniced at first as i thought i had updated ALL rows, but luckily this seems not to be the case.
And can i turn this off??
Many thanks,
Martin
View 5 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Aug 30, 2004
I am new to stored procedures and T-SQL so please stick with me. I have a table that holds information about companies. I am trying to write a stored procedure that when run will query that table and find out if there are more than one entry of that company. All company names in that table must be unique (they can only occur once), if they occur more than once I need to flag it for reporting.
So what is the best way to go about this? Essentially what i was thinking was doing a select * on the table and then going from the first entry to the last and at each entry running a select * from table where companyname = @nameofcompany. @nameofcompany would be the name for that entry. If the select statement revealed more than one entry then i would know there was a problem.
Like I said I am new and this is probably very simple but i need a little help getting started
thanks
View 6 Replies
View Related
Jun 6, 2008
Hey all - VERY new to SQL so I apologize if I butcher normally trivial things :)
Looking to run a query that will retrieve the number of results returned from a select statement...
Currently have a LicenseID table with a Software column...the statement that works on it's own that i've got is:
SELECT * FROM Software WHERE LicensesID = 2
Currently when I run that with the data so far I get 4 results returned to me...how can I add to that statement so that instead of displaying the results themselves, I just get the number 4 returned as a total number of results?
Thanks all!
View 2 Replies
View Related
Dec 16, 2013
I have a table with three columns. Name, book#, category.
I need to write a select statement that looks at the book# and category and returns all the names that match that into one joined our combined result. So if my name column had john, Jane, Jim, jack, and Steph in unique rows that matched it would bring back a resulted formatted as. John, Jane, Jim, jack, Steph. So I could place that in a field for reference.
View 4 Replies
View Related
Mar 15, 2008
After running a select statement in vb 6, how do i send the results to the printer. Here is the code i've written so far:
Private Sub ProcSelectRecords()
Dim MyConn As ADODB.Connection
Set MyConn = New ADODB.Connection
Dim MyRecSet1 As New ADODB.Recordset
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:DocumentsA2 Computing Courseworkdb1.mdb"
MyConn.Open
Set MyRecSet1 = MyConn.Execute("SELECT * FROM tblItem_Sales")
MyConn.Close
End Sub
Im using MS Access as my backend
any help would me much appreciated.
View 1 Replies
View Related
Jun 6, 2006
I am using the following conditional select statement but it returns no results.
Declare @DepartmentName as varchar
Set @DepartmentName = null
Declare @status as bigint
Set @status = 4
IF (@DepartmentName = null) BEGIN
SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM AdminView
WHERE (Status = @status)
ORDER BY CallLoggedDT
END
ELSE IF (@DepartmentName <> null) Begin
SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM dbo.AdminView
WHERE (Status = @status) AND
(DepartmentName = @DepartmentName)
ORDER BY CallLoggedDT
end
when i run the 2nd half by itself it tells me to declare @status but not @departmentname. whats going on???
Chris Morton
View 3 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
Jun 27, 2006
I have the following SELECT statement attached to a dropdown box:
SELECT [Workshop] FROM [Workshops] WHERE Workshop <> (SELECT Workshop FROM Workshop_Registration WHERE FullName = @FullName AND Completed = @Completed) ORDER BY [Workshop]
I am trying to get all workshops (50 or more) from the WORKSHOPS table that the logged in user is not already registered for. This works perfectly as long as the student is registered for at least 1 class. It populates the dropdown with all of the other classes. If they aren't registered for a class then it doesn't list any classes. The problem is definitely the subquery, but how do I make it to where if the subquery doesn't return any results (student not registered for anything), I get all of the workshops in the dropdown? Any help is appreciated!
MikeD
View 2 Replies
View Related
Feb 2, 2008
Hello,I have written a stored procedure where I prompt the user for the Year and the Month (see below)How do I take the variable @TheMonth and find out how many days is in the month and then loop to display a total for every day in the selected month.Can someone please point me in the right direction.
CREATE PROCEDURE crm_contact_frequency_report
@TheYear varchar(4),@TheMonth varchar(2)
AS
SELECT
/* EMAILS (B) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 1)) AS Total_EmailOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 0)) AS Total_EmailImconing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode IS NULL)) AS Total_EmailNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)) AS Total_All_Emails,
/* PHONE CALLS (C) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 1)) AS Total_CallOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 0)) AS Total_CallIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode IS NULL)) AS Total_CallNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)) AS Total_All_Calls,
/* FAXES (D) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 1)) AS Total_FaxOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 0)) AS Total_FaxIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode IS NULL)) AS Total_FaxNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)) AS Total_All_Faxes
FROM CampaignResponse AGO
View 2 Replies
View Related
Feb 11, 2015
I need to insert data into a table based on the results returned by a select statement. Basically, the select statement below gives me a list of all the work orders created in the last hour.
select worknumber from worksorderhdr where date_created > DATEADD(HOUR, -1, GETDATE())
This might return anywhere between 5 and 50 records each time. What I then need to do is use each of the work numbers returned to create a record in the spec_checklist_remind table. The other details in the insert statement will be the same for each insert, it's just the worknumber from the select statement that needs to be added to the insert where the ?? are below:
INSERT INTO spec_checklist_remind (form, record_type, linked_to_worknumber, spec_checklist_id) values (5, 0, '??',52)"
View 2 Replies
View Related
Jul 23, 2005
I need to run a stored procedure on all the results of a select query.For a simplified example, I'd like to be able to do something likethis:SELECT JobID, QtyToAddFROM JobsWHERE QtyToAdd > 0Then for the results of the above:EXEC sp_stockadd @JobID, @QtyToAddWhere the above line ran for every result from the above select query.Anyone able to shed some light on how to do this?Many thanks,James
View 2 Replies
View Related
Feb 11, 2008
I'm new to sql stored procedures but I would like to store the results
of an sql statement in a variable such as:
SET @value = select max(price) from product
but this does not work, can someone tell me how I would go
about in storing the results in a variable.
@value is declared as int
Thanks in advance,
Sharp_At_C
View 1 Replies
View Related
May 21, 2006
I have concatenated a long Select Statement and assigned it to a variable. (i.e.)
@a = Select * from foo ---Obviously mine is much more robust
How do I execute @a to actually Select * from foo?
View 3 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
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
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
Aug 10, 2006
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far:
SELECT
l.loanid,
p.investorid,
l.duedate,
case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate,
pc.interestrate
FROM loan l
inner join participation p on p.loanid = l.loanid
inner join paymentchange pc on pc.loanid = l.loanid
where p.investorid = '12345' and RateDueDate is not null
order by l.loanid, pc.duedate
I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated.
Thanks!
View 6 Replies
View Related
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
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
Aug 22, 2006
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.
Here is my select statement:
SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate -
(SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name,
department.department_name, location.location_name
FROM employee INNER JOIN
request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN
department ON employee.emp_department = department.department_id INNER JOIN
location ON department.department_location = location.location_id
GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial,
employee.emp_lname, department.department_name, location.location_name
ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information:
SELECT (SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!!
TIA
View 3 Replies
View Related
Dec 4, 2014
I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
View 4 Replies
View Related
Aug 7, 2007
We are getting unexpected results from the following update statement when it is executed on SQL Server 2005.
The strange thing is that we get duplicated values for QM_UID (although when run under SQL Server 2000 we don't get duplicated values)
Can anyone explain why this happens or suggest another way of populating this column without using a cursor or adding a temporary autoincrement column and copying the values over?
declare @NextID int;
set @NextID = 1;
update tmp set QM_UID=@NextID, @NextID = @NextID + 1;
select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID
QM_UID count(*)
25 2
26 3
27 4
28 4
29 4
30 4
31 4
32 4
33 4
34 4
35 5
36 4
37 4
38 4
39 4
40 3
...
--- Script to replicate problem
-- NB: The number of rows that must be added to tmp before this problem will occur is machine dependant
-- 100000 rows is sufficient on one of our servers but another (faster) server doesn't show the error
-- at 100000 rows but does at 1000000 rows.
-- Create a table
CREATE TABLE tmp (
[QM_ADD_OP] [char](6) DEFAULT '' NOT NULL,
[QM_ADD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_ADD_TIME] [int] DEFAULT -1 NOT NULL,
[QM_EDIT_OP] [char](6) DEFAULT '' NOT NULL,
[QM_EDIT_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_EDIT_TIME] [int] DEFAULT -1 NOT NULL,
[QM_LOCK_OP] [char](6) DEFAULT '' NOT NULL,
[QM_QUOTE_JOB] [smallint] DEFAULT 0 NOT NULL,
[QM_QUOTE_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_JOB_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_PRJ_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_NUMBER] [char](12) DEFAULT '' NOT NULL,
[QM_REV_NUM] [char](6) DEFAULT '' NOT NULL,
[QM_REV_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REV_TIME] [int] DEFAULT -1 NOT NULL,
[QM_REV_OPR] [char](6) DEFAULT '' NOT NULL,
[QM_STYLE_CODE] [char](4) DEFAULT '' NOT NULL,
[QM_REP_JOB_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_REP_COLUMN] [smallint] DEFAULT 0 NOT NULL,
[QM_REP_PART] [char](6) DEFAULT '' NOT NULL,
[QM_REP_MODEL] [smallint] DEFAULT 0 NOT NULL,
[QM_REP_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_MODEL_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_RUN_NUM] [int] DEFAULT 0 NOT NULL,
[QM_SOURCE_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_SOURCE_VAR] [smallint] DEFAULT 0 NOT NULL,
[QM_SOURCE_QTY] [char](12) DEFAULT '' NOT NULL,
[QM_SOURCE_PART] [char](6) DEFAULT '' NOT NULL,
[QM_SOURCE_MODEL] [smallint] DEFAULT 0 NOT NULL,
[QM_ORIG_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_ORIG_VAR] [smallint] DEFAULT 0 NOT NULL,
[QM_ORIG_QTY] [char](12) DEFAULT '' NOT NULL,
[QM_ORIG_PART] [char](6) DEFAULT '' NOT NULL,
[QM_COPY_JOB] [char](12) DEFAULT '' NOT NULL,
[QM_COPY_COLUMN] [smallint] DEFAULT 0 NOT NULL,
[QM_COPY_J_PART] [char](6) DEFAULT '' NOT NULL,
[QM_COPY_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_COPY_VAR] [smallint] DEFAULT 0 NOT NULL,
[QM_COPY_QTY] [char](12) DEFAULT '' NOT NULL,
[QM_COPY_Q_PART] [char](6) DEFAULT '' NOT NULL,
[QM_JOINT_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_QUOTE_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_JOB_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_LIVE_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_USER_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_DEL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_IS_CONVERTED] [smallint] DEFAULT 0 NOT NULL,
[QM_PRINTED] [smallint] DEFAULT 0 NOT NULL,
[QM_COPY_RATES] [smallint] DEFAULT 0 NOT NULL,
[QM_IMPORT_UPDATE] [smallint] DEFAULT 0 NOT NULL,
[QM_CRED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_CRED_TIME] [int] DEFAULT -1 NOT NULL,
[QM_CRED_AMT] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_CRED_OP] [char](6) DEFAULT '' NOT NULL,
[QM_HELD] [smallint] DEFAULT 0 NOT NULL,
[QM_PROOF] [char](12) DEFAULT '' NOT NULL,
[QM_DELIV_METHOD] [char](12) DEFAULT '' NOT NULL,
[QM_ART_METHOD] [char](12) DEFAULT '' NOT NULL,
[QM_DES_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_REC_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REC_TIME] [int] DEFAULT -1 NOT NULL,
[QM_OWN_OP] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_RESP_TIME] [int] DEFAULT -1 NOT NULL,
[QM_RESP_OP] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_1] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_2] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_3] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_4] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_5] [char](6) DEFAULT '' NOT NULL,
[QM_RECONTACT] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REQ_FLAG] [smallint] DEFAULT 0 NOT NULL,
[QM_ORIG_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_ORIG_TIME] [int] DEFAULT -1 NOT NULL,
[QM_PREF_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_PREF_TIME] [int] DEFAULT -1 NOT NULL,
[QM_LATE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_LATE_TIME] [int] DEFAULT -1 NOT NULL,
[QM_TITLE] [char](72) DEFAULT '' NOT NULL,
[QM_DELIV_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_CLT_SPEC] [char](12) DEFAULT '' NOT NULL,
[QM_TAX_REF] [char](22) DEFAULT '' NOT NULL,
[QM_CONTACT] [char](36) DEFAULT '' NOT NULL,
[QM_PHONE] [char](22) DEFAULT '' NOT NULL,
[QM_FAX] [char](22) DEFAULT '' NOT NULL,
[QM_ORDER] [char](20) DEFAULT '' NOT NULL,
[QM_ORDER_CFM] [smallint] DEFAULT 0 NOT NULL,
[QM_ORDER_REL] [char](6) DEFAULT '' NOT NULL,
[QM_REP] [char](12) DEFAULT '' NOT NULL,
[QM_REP_1] [char](12) DEFAULT '' NOT NULL,
[QM_REP_2] [char](12) DEFAULT '' NOT NULL,
[QM_REP_3] [char](12) DEFAULT '' NOT NULL,
[QM_REP_4] [char](12) DEFAULT '' NOT NULL,
[QM_REP_5] [char](12) DEFAULT '' NOT NULL,
[QM_COORDINATOR] [char](12) DEFAULT '' NOT NULL,
[QM_PRIORITY] [smallint] DEFAULT 0 NOT NULL,
[QM_TYPE_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_GRADE] [smallint] DEFAULT 0 NOT NULL,
[QM_FIN_SIZE_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_FIN_WID] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_LEN] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_DEP] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_GUSS] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_GSM] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_UNIT] [char](12) DEFAULT '' NOT NULL,
[QM_ORIENT] [smallint] DEFAULT 0 NOT NULL,
[QM_PROD_CODE] [char](22) DEFAULT '' NOT NULL,
[QM_FIN_GOOD] [char](22) DEFAULT '' NOT NULL,
[QM_CUST_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_CUST_CODE_1] [char](12) DEFAULT '' NOT NULL,
[QM_CUST_CODE_2] [char](12) DEFAULT '' NOT NULL,
[QM_CUST_PROS] [smallint] DEFAULT 0 NOT NULL,
[QM_REQD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REQD_TIME] [int] DEFAULT -1 NOT NULL,
[QM_FOLIO] [char](12) DEFAULT '' NOT NULL,
[QM_FOLIO_1] [char](12) DEFAULT '' NOT NULL,
[QM_FOLIO_2] [char](12) DEFAULT '' NOT NULL,
[QM_PACK_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_USAGE] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_REORDER] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_EACH_WGT] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_WGT_UNIT] [char](12) DEFAULT '' NOT NULL,
[QM_RFQ_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_RFQ_TIME] [int] DEFAULT -1 NOT NULL,
[QM_RFQ_OPR] [char](6) DEFAULT '' NOT NULL,
[QM_SALES_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_SALES_SRC] [char](12) DEFAULT '' NOT NULL,
[QM_SALES_RSN] [char](12) DEFAULT '' NOT NULL,
[QM_PROFILE] [char](12) DEFAULT '' NOT NULL,
[QM_JOB_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_PREV_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_JOB_UNIT] [char](12) DEFAULT '' NOT NULL,
[QM_PO_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_PO_TIME] [int] DEFAULT -1 NOT NULL,
[QM_PO_OP] [char](6) DEFAULT '' NOT NULL,
[QM_DLY_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_DLY_TIME] [int] DEFAULT -1 NOT NULL,
[QM_QTY_DESP] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_TOTAL_DLY] [int] DEFAULT 0 NOT NULL,
[QM_SCHED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_SCHED_TIME] [int] DEFAULT -1 NOT NULL,
[QM_CLOSE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_CLOSE_TIME] [int] DEFAULT -1 NOT NULL,
[QM_INV_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_PACK_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_DOWN_LOAD] [smallint] DEFAULT 0 NOT NULL,
[QM_TRACK_CODE] [char](4) DEFAULT '' NOT NULL,
[QM_TAX_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_TAX_CODE] [char](6) DEFAULT '' NOT NULL,
[QM_CURR] [char](6) DEFAULT '' NOT NULL,
[QM_EXCH_RATE] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_UNIT_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_UNIT_FLAG] [smallint] DEFAULT 0 NOT NULL,
[QM_RUNON_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_SPEC_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_CHARGEABLE] [smallint] DEFAULT 0 NOT NULL,
[QM_NC_REASON] [char](22) DEFAULT '' NOT NULL,
[QM_CUST_MKUP] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_JOB_MKUP] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_BROKERAGE] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_CUST_DISC] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_INVOKED_BTNS] [int] DEFAULT 0 NOT NULL,
[QM_IMPORTED] [smallint] DEFAULT 0 NOT NULL,
[QM_IMPORT_RECALC] [smallint] DEFAULT 0 NOT NULL,
[QM_IMPORT_CONVERT] [smallint] DEFAULT 0 NOT NULL,
[QM_BRANCH] [char](6) DEFAULT '' NOT NULL,
[QM_CODE] [char](36) DEFAULT '' NOT NULL,
[QM_TEMPLATE] [smallint] DEFAULT 0 NOT NULL,
[QM_REPEAT_PERIOD] [int] DEFAULT 0 NOT NULL,
[QM_REOPEN_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_CAT_OPTION] [char](16) DEFAULT '' NOT NULL,
[QM_UNIT_ID] [char](10) DEFAULT '' NOT NULL,
[QM_PROD_BRANCH] [char](6) DEFAULT '' NOT NULL,
[QM_UID] [int] DEFAULT 0 NOT NULL,
[QM_AVAIL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_AVAIL_TIME] [int] DEFAULT -1 NOT NULL
) ON [PRIMARY]
GO
-- Create an index on the table
CREATE unique INDEX [QM_NUMBER_ORDER] ON tmp([QM_QUOTE_JOB], [QM_NUMBER]) ON [PRIMARY]
GO
-- Populate the table
declare @Counter as int
SET NOCOUNT ON
set @Counter = 1
while @Counter < 100000
begin
insert into tmp (QM_ADD_TIME, QM_NUMBER) values (1,@Counter);
set @Counter = @Counter + 1
end
GO
-- Update QM_UID to a sequential value
declare @NextID int;
set @NextID = 1;
update tmp set QM_UID=@NextID, @NextID = @NextID + 1;
-- Find rows with a duplicate QM_UID (there should be no duplicate)
select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID
--drop table tmp
-- output from select @@VERSION
-- Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
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
Feb 8, 2008
How to Use select statement when using Update statement.
Eg: Update program_video set program_id=(select id from program where recordname='122'), stopnumber=stopnumber*-1 where stopnumber < 0
Thanks & Regards
Jagadeesh
View 7 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
Dec 3, 2004
Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?
For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...
ALTER PROCEDURE u_sp_x
@semester int
AS
Select Semester@semester
From ThisTable
Just curious.
Thanks,
Steve Hanzelman
View 6 Replies
View Related
Nov 15, 2013
Update ed_abcdeeh set category = case when name_of_school = '' then category = 'No Facility' else '' end,status = case when name_of_school = '' then status = 'Non-Compliant' else 'Compliant' end.
How to make this query right.. when name of school is blank i want to update my category to No facility, but if the name of school has data it will just make it blank. same to the status..
VFP9.0 via MySQL 5.0
View 5 Replies
View Related
Mar 24, 2015
I would like to UPDATE a column form my SELECT statement below but not sure how to go about this?!
The column I need to update is: courses.active = N
SELECT schools.id, schools.name, schools.cactus_name, schools.cactus_name_english, schools.address1, schools.address2, schools.city, schools.county, schools.postcode, schools.country, schools.active, schools.latitude, schools.longitude, schools.contact, schools.website, schools.email, schools.telephone, schools.fax, schools.dos, schools.other_contacts, schools.school_commission, schools.bo_notes, courses.name, courses.domains,
[Code] ....
View 7 Replies
View Related
Jul 23, 2005
I have the following list of fields in a table named call.CallID, CallGroupID, PersonID, ParentID,I have two records.23, 45, John, Null26, <Incorrect Value>, Jane, 23Now, I'm wanting to set the CallGroupID of record two, to that of theCallGroupID of record one.This is what I came up with, but it doesn't work.update call set CallGroupID = (Select CallGroupID from call whereCallID = ParentID) where ParentID is not nullAs you know the Select statment I have embedded is trying to pull up alist CallGroupIDs from records where the CallID = ParentID. What Iwant it to do is pull up the CallGroupID of the record where the CallID= ParentID.The CallID is a unique field, so there is only one such record in thetable.Any help?Thanks
View 2 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
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
