hi, everyone,
When I update a row that does not exist in a table using VBscript and SQL 2003 server, the row is automatically added to the table. Why does this happen?
Can somebody help me? Thanks in advance!
When I execute following SQL Query Analyzer (SQL 2005) i am getting message 3 times "95 Records effected" in query analyzer message window.
UPDATE dbo.Load_AC_Install
SET Load_AC_Install.Rejected_Reason = Load_AC_Install_Rejected.Validation_Reason
FROM (Load_AC_Install JOIN Load_AC_Install_Rejected
ON Load_AC_Install.Hardware_Portfolio_Barcode = Load_AC_Install_Rejected.Hardware_Portfolio_Barcode
AND Load_AC_Install.Install_Model_Barcode = Load_AC_Install_Rejected.Model_BarCode )
After I load "Load_AC_Install" table into Temporary table #Load_Install and When I execute following SQL Query from SQL Query Analyzer using temporary table I am getting message Only 1 time "95 Records effected"
Update #Load_Install
SET #Load_Install.Rejected_Reason = Load_AC_Install_Rejected.Validation_Reason
FROM (#Load_Install JOIN Load_AC_Install_Rejected
ON #Load_Install.Hardware_Portfolio_Barcode = Load_AC_Install_Rejected.Hardware_Portfolio_Barcode
AND #Load_Install.Install_Model_Barcode = Load_AC_Install_Rejected.Model_BarCode )
So My Question is if i use phicical table (dbo.Load_AC_Install) then i get message 3 times, but If i use same data with tempoary table(#load_Install) I get message only one time "95 Records Effected" in query analyzer message window!
Anyone know why?
Thanks for response and please ask me if you still have any questions!
I am trying to concatenate two columns together firstname, lastname
i have done some sql in oracle, but i'm noticing that mssql is different
what i have seen is
select firstname||lastname from table
that errors, what is the right syntax?
Thanks
I am attempting to do an insert on the DataGrids Delete command. The problem I am running into is that im not very good at SQL inserts.
Can anyone tell me how to insert a record into a table based on the ID of a record in another table? I want to do this.
As a record (datagrid Row) is deleted, I want to run code (that I dont know how to write) that does this...
Insert into Table-Interface-LOG (fields), from Table-Interface (fields)
Where Interface.Interface_ID = @interface_id
I just need the SQL to do the insert, no ADO code or anything. I dont understand how to use the SET in a SQL command.
What I have so far that Doesnt work.. not even sure why it doesnt work..
INSERT INTO InterfaceLog
SELECT Interface_ID, App_ID, SendsToApp_ID, InterfaceName, Description, ITContact_ID, Type, Frequency, DataFormat, Status, CommMethod, Complexity,
ProdEntryDate, ProdExitDate
FROM Interface
WHERE (Interface_ID = @interface_id)
any Ideas? Do I use the set comand somehow here?
Thanks for any code...
Hi, I feel a difficulty to understand the following procedure, it is simple but i am not sure the logic, could anyone give me some explain / hints , then I know how to write the similiar procedure next time. thanks for your time. I extract the code from the Classifieds Starter Kit
For the procedure of GetCategoryByParentIdWHERE (ParentCategoryId = @ParentCategoryId)OR (@ParentCategoryId = 0 AND ParentCategoryId IS NULL)
For the procedure of GetParentCategoryById WHERE (SELECT Path FROM Category WHERE Id = @Id) LIKE Path + '%'
ALTER PROCEDURE GetCategoryByParentId(@ParentCategoryId int = 0)ASSET NOCOUNT ON;SELECT [Id], [ParentCategoryId], [Name], NumProductsFROM CategoryWHERE (ParentCategoryId = @ParentCategoryId)OR (@ParentCategoryId = 0 AND ParentCategoryId IS NULL)ORDER BY NameALTER PROCEDURE GetParentCategoryById @Id intASSELECT Id, ParentCategoryId, Name, NumProductsFROM CategoryWHERE (SELECT Path FROM Category WHERE Id = @Id) LIKE Path + '%'ORDER BY Path
Thanks a lot, Trevor
I need to see whether there's any unapproved timecards, and if so compile a list of supervisors that need to make approvals.
my original statement was:
SELECT DISTINCT
EmployeeMaster.SupervisorNumber
, SupervisorMaster.EmailAddress
, SupervisorMaster.EmployeeMasterNumber
, RTRIM(SupervisorMaster.FirstName) + ' ' + RTRIM(SupervisorMaster.LastName) AS SupervisorName
FROM
Cards
LEFT OUTER JOIN EmployeeMaster ON Cards.EmployeeNumber = EmployeeMaster.EmployeeNumber
LEFT OUTER JOIN SupervisorMaster ON EmployeeMaster.SupervisorNumber = SupervisorMaster.EmployeeNumber
WHERE
(Cards.SupApp = 0)
And this worked great until they asked for a date range.
If I am correct the distinct will look for the first occurrence that meets the select criteria and ignore the rest of the rows that meet the criteria. Leading to the possiblity that the first row is not a timecard that meets the date requirement, and even though other timecards exist that will satisfy the condition, they will not be tested because of the Distinct clause.
What's the best way to handle a situation like this?
Hi.
Please help me.
Table 'Counter' has a date field and an integer field. I want to auto increase the integer field by 1 using the update statement. I DO NOT want to first run a select statement to find out the initial value of the integer field prior to incrementing it. how would i go about doing this?
Thanks a lot!
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 RelatedOk 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.
I have a products database on my online store that records 2 columns for each product:DateAdded: the datetime the item was addedTotalSales: the total sales generated since the item was added to the siteI have already figured out how to calculate best sellers with this formula:TotalSales / TotalDays I am adding a computed column to my database, I need to calculate the followingTotalSales / ("this is where i need something to use today's date and calculate how many days it has been since it was added")Then if I can do that, I will automatically have a column that will give me a number ranking each of my items.
View 2 Replies View RelatedOk, I do know SQL and have been using it for quite soem time. For some reason, it is giving me an error and I was wondering if someone could help.
Here is the few lines of interest
System.Data.SqlClient.SqlCommand command;
command = new System.Data.SqlClient.SqlCommand(@"SELECT Password FROM User WHERE Username='" + user + "'", this.sqlConn);
dataReader = command.ExecuteReader();
This is the error I am coming up with.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'User'.
I have tried enclosing the Table name in quotes and removing the @. What am I doing wrong? Thanks!
I have a textbox with the id of txtName it is a name text box.
and I have a submit button.
How do i get the value of the text in the text box to popluate a new row of data in my Sql Server database.
Here are my tables:
-------------------------------
Events
-------------------------------
ID | E_EventName
-------------------------------
-------------------------------
Photos
-------------------------------
ID | P_EventID | P_Filename
-------------------------------
Given an event like "2005 cookout", and a file like "bob.jpg", I'd like to insert the event id based on the event name and the filename into Photos. How can I do that?
Hello well-intentionned reader,
I'm having trouble figuring out what's wrong with my statement. This is what the table looks like. I want to display employees under the manager with the last name King. my statements are below the tableName Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
MANAGER_ID NUMBER(6)
My statement
SQL> SELECT last_name, salary
FROM employees
WHERE manager_id =
(SELECT employee_id
FROM employees
WHERE EMPLOYEE.LAST_NAME = .King.); 2 3 4 5 6
WHERE EMPLOYEE.LAST_NAME = .King.)
*
ERROR at line 6:
ORA-00936: missing expression
Thank you for your time.
/gozu
Hi everyone and thank for taking the time to read, I am new to this forum and to SQL to.
I am taking a programmers course, i learned mainly C# for now, but i also know a bit of C and C++ and linux programming,
i am going through my SQL school book, (SQL Server 2000 design and implementation) and i am trying a simple IF statement. heres how it goes
DECLARE @P CHAR
SET @P=NULL
SELECT @P=Powerlck FROM vehicle
IF (@P=NULL)
PRINT 'got you'
powerlck is a collum taking char(1) which is either Null or 1.
there is five entry all but one is set to 1, null is the default
when i run this script, it does not print 'got you'
but if i run select * from vehicle
it will show one with <NULL> value.
what did i miss??
I've been struggling with this problem, hope someone can help. I'm using SQL Server 2005 Express. I have a simple database with 7 tables (m64,m67,m69,m71,m87) all have an identical design(column name, type).
All I want to do is pull out one recordset (if it's in that table) from each of the tables.
This is what I have:
"SELECT * FROM m64, m67, m69, m71, M87 WHERE P_N Like '" & Request.Querystring("P_N") & "'"
If I only enter one table, I get a result.
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
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'.
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View 1 Replies View RelatedIt appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
View 5 Replies View RelatedHi..This may be silly but i am stuck at this for some time.
I have 10000 rows in a table and i want to copy the value of column 1 in all the rows to column 2 ( which contains NULL presently in all the rows ). The two columns are of the same datatype.
Thanx.
Hello all and I look forward to reading your replys. Currently, I'm trying to update a table in my SQL database by writing a script that will go get the field name and correct info, verify it has the correct field by using two other columns and then update the information.
I do not know how to use tell SQL what to update when the field name can change. I've used the following in the past.
UPDATE [Import Table]
SET [Import Table].[???] = [Correction_Table].[Correct_Info]
WHERE [Import Table].[UNIQUE ID] in (select [USO Unique ID] from [Correction_Table])
IN this new instance, I'm reading the Field_Name from the Correction_Table so I don't know what to put in my SET statement.
Please any help or pointers in the right direction would be greatly appreciated.
Battle_n
I have a table Products with their prices, and I have another table Sales, that have the prices of the products but they are null, I need to make an update that update the tables Sales, inserting the prices from the table Products
View 3 Replies View RelatedI need to do an update. Can you tell me if it's possible to update multiple tables in a single statement when joined, as with a select, or this has to be taken care of in seperate statements with the same join? I'm sure this is a simple YES / NO answer.
Here is what I'm using, with the same structure as somewhere else... why do I get 'incorrect syntax near q' on that very first line?
-- Do the update
update ccs_quotes q
set ccs_quo_user_date#1 = j.completed
from "ken-sql-002".hettonhosttestdatabase.dbo.job j
inner join ccs_quotes q
on left(j.cid,10) = q.ccs_quo_ref_number#2
where completed is not null
and ccs_quo_ref_number#2 = 'SHGD40021'
Thanks a lot
Mike
I need to copy data from one column that has a max size of 45 characters toa column that has a max size of 20 characters.What is the syntax to say fill the first 20 characters out of 45 fromcolumn 1 into column 2?jeff--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedHello,
I am looking to modify this Case Statement. Where it says ELSE '' I need it to display the actual contents of the cell. 1 = Yes , 0 = No, (any other integer) = actual value.
Right now if the value is anything other than 1 or 0, it will leave the cell blank.
CASE dbo.Training.TrainingStatus WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END AS TrainingStatus
Thank You.
Hello and thanks for looking! I have this sql stament:
Dim strInsertCommand As String = "INSERT INTO CurrentDrawVar (Username, GamesBought, DateLastBuy) VALUES ('" & UserID & "', '" & gamestobuy & "', '" & Now & "')"
It works great and inserts into a database like it should. My question is where can I insert a WHERE stament into it so that I can insert this data over information that already exists in the database. FOr instance. I have a user grogo21 and in this row I want to insert gamestobuy and now. Where can i put this and is the stament below right:
WHERE Username = 'grogo21'
I tried putting it after the parenthases after the values but it doesnt work. Thanks alot!!
This should be terribly simply, but I just can't seem to figure out where I'm going wrong. I have a table that logs usage of a few software licenses in my division, so it has columns for the username, the license, and the date/time checked out and date/time checked in. I have written a quick SQL query that totals how much time all the users have spent in three of the licenses during this work week. It works fine. But now I just want to limit that query to those users who have used those licenses for more than two hours.
Sounds simple enough, but I must be overlooking something obvious. I have tried [Minutes]>120 in both a WHERE and a HAVING statement, but neither worked. I have also tried changing the name of the column to something other than Minutes in the off case that there might be some kind of keyword confusion, but it didn't work. I have also tried various other unlikely fixes but continually get the error "Invalid Column Name 'Minutes'".
Any help would be very appreciated since this is driving me nuts. The code that works is below.
Declare @datMin datetime
Declare @datMax datetime
set @datMin=DateAdd(mi,
-(DatePart(hh,Getdate())*60 + DatePart(mi,GetDate())),--Gets the current minute of the day
DateAdd(dd,-datepart(dw,GetDate())+1,GetDate()))--Gets First Day of the Week at this time
set @datMax=DateAdd(mi,
1439-(DatePart(hh,Getdate())*60 + DatePart(mi,GetDate())),
GetDate())
Select UserName, Sum(Case
When DateOut<@datMin and DateIn<@datMax and DateIn>@datMax
Then datediff(n, @datMin, DateIn)
When DateOut<@datMin and DateIn>@datMax
Then datediff(n, @datMin, @datMax)
When DateOut>@datMin and DateOut<@datMax and DateIn>@datMax
Then datediff(n, DateOut, @datMax)
Else datediff(n, DateOut, DateIn)
End) as [Minutes]
From [Log]
Where ((DateOut>=@datMin and DateOut<=@datMax)
or (DateIn>=@datMin and DateIn<=@datMax)
or (DateOut<@datMin and DateIn>@datMax))
And (License='Viewer' or License='ARC/INFO' or License = 'Editor')
--And ([Minutes]>120)
Group by UserName
--Having ([Minutes]>120)
Order by [Minutes] desc
Hi,
I have several records in a table of "links", which has the fields ID, UserID, Description and URL. I'm trying to select all the records in this table for a specific user. The stored prodecure I'm using is:
ALTER PROCEDURE dbo.sprocLinkSelect (@UserID uniqueidentifier)
AS
SELECT ID, UserID, description, URL
FROM links
WHERE UserID = @UserID
When I execute the procedure without "UserID" in the SELECT part of the statement it works and returns the correct rows for the UserID entered as a parameter. However, when UserID is included in the SELECT part of the statement (as above), it says:
"No rows affected.
(1 row(s) returned)"
and just shows the column headings, but with no record details beneath. (When the example works as intended, it returns two rows.)
I've tried changing the procedure to take @description as its parameter and return all records with one specific description, and it also only works when UserID isn't included in the SELECT line. I've also tried using SELECT *, but that doesn't seem to work either.
Any help would be greatly appreciated, I'm using SQL Express.
Well my problem lies in that I am generating reports with the data I retrieve from my sql database. However my problem resides in the fact that I am generating one report at a time and if I want to grab each entry in the order in which they were produced its no problem.
IE - Using PowerBuilder 10.0 as an IDE for my application to generate reports.
select i_id into :insp_id from inspection where i_id = :index order by i_id asc using sqlca;
But now if I want to grab them in alphabetical order from another table I have problems.
this is the code I am trying to use maybe I am just thinking it through wrong.
select s_id into :insp_id from section where s_id = :index order by s_name asc using sqlca;
any help is appreciated. Is there a way to grab each row in alphabetical order?
I have an IIF statement in a text box. Its very basic
What I want is to check if the value of something is greater than 1. If it is, then make it 1. If it isn't then just use what the value is.
(loose example)
=IIF((Field!Var1.Value * Field!Var2.Value)>1, 1,Nothing)
Basically I can't have a pecentage greater than 100. If it is then make it 100. If its not then just use the result of the calculation.
I have a table with the name of customers in it. The problem is that I have one column with the full name in it like
|Last First Middle|.
As you can see this is a problem for me.
I need to find a way to put these values into 3 seperate columns.
|Last| First| Middle |
Is there a function to seperate them via a space?
I have been looking on the web and have not found any functions or solutions.
Any help would be most appreciated.
Thanks in advance,
Gene
I am very new to sql and would like some help with a simple select statement. I am trying to pull birthdates using the following code. I can get the query to run but some of the dates are not between the parameters. What am i doing wrong?
SELECT Employees.EmployeeStatusID, People.FirstName, People.LastName, People.BirthDate
FROM Employees FULL OUTER JOIN
People ON Employees.SystemAssignedPersonID = People.SystemAssignedPersonID
WHERE (People.BirthDate BETWEEN '10 - 01 - 1900' AND '12 - 15 - 2007')
ORDER BY People.BirthDate
<<<<<<<<<<<<<<<<<<<<<<<<<< Results
A
KWANG
TAK
10/25/1929 12:00:00 AM
T
ETHEL
MOE
5/24/1933 12:00:00 AM
T
GILBERT
BARAJAS
1/9/1937 12:00:00 AM
A
EDILBERTO
PENA
2/10/1937 12:00:00 AM