Simple Update Query Help
Feb 27, 2007
Hi, wondered if anyone here can help out with an SQL query thats been driving me mad. Currently I do this programmatically in a C# program with a hard coded mapping class but want to put the new codes into a table and run an update query instead.
2 tables like this;
Table: CarModels
SORT
GROUP_CODE
MODEL_NAME
PK: SORT
Table: NewGroupCodes
OLD_CODE
NEW_CODE
PK: OLD_CODE, NEW_CODE
Basically I want to update all GROUP_CODE entries in the CarModels table with the NEW_CODE values from NewGroupCodes where there is a match between CarModels.GROUP_CODE and NewGroupCodes.OLD_CODE.
thanks in advance,
afro
View 7 Replies
ADVERTISEMENT
Feb 5, 2007
hi, i've just started using sql server 2005 and visual studio, i have made 3 stored procedures, an update, insert and delete. The update one doesn't work but the others do so i'm on the right track with them..
is there anything obvious i am missing in the following update stored procedure? All of the datatypes and names line up with the datatypes/sizes/names in the table..
Code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spUpdateAddresses]
-- Add the parameters for the stored procedure here
(@addressID int, @fname varchar(50), @lname varchar(50), @address01 varchar(50), @address02 varchar(50), @suburb varchar(50), @state varchar(50), @postcode char(10), @comments varchar(1250), @phone char(10), @fax char(10), @mobile char(10))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Update addresses
set @fname=fname, @lname=lname, @address01=address01, @address02=address02, @suburb=suburb, @state=state, @postcode=postcode, @comments=comments, @phone=phone, @fax=fax, @mobile=mobile
where (@addressID = addresses.addressID)
END
View 2 Replies
View Related
Oct 5, 2006
Can we add if statements in Update query?
I have to do following task in one stored proc:
I am passing 2 param to the stored proc @spkid, @Repspk
Update myTable
set spk1=@Repspk
where spk1=@spkid
Update myTable
set spk2=@Repspk
where spk2=@spkid
Update myTable
set spk3=@Repspk
where spk3=@spkid
Instead of writing 3 update statements what is the other option that will make the query run faster? Thanks alot for transfering knowledge...
View 1 Replies
View Related
Nov 20, 2006
Hi,
I have a table called "Cust_Vehicle_Specialisation" consisting columns named "Vehicle_Specialisation_Id", "Motortrade_Id" and Effectdate(datetime).
My data in the table consists like this..
Vehicle_Specialisation_Id Motortrade_Id Effectdate
A94ACDEE 007094C1 2006-05-16 13:01:00.000
2EC8B2BF 007094C1 2006-06-05 12:19:00.000
A1C617D2 007094C1 2006-06-12 17:37:00.000
458C5F70 007094C1 2006-07-25 09:44:00.000
139954DE 007094C1 2006-08-01 12:05:00.000
1F3D7BDB E1A50F16 2006-05-22 12:00:00.000
F85EA44A E1A50F16 2006-07-14 15:05:00.000
489700E8 E1A50F16 2006-08-21 12:59:00.000
I want to update the Vehicle_Specialisation_Id records for the same Motortrade_Id and the minimum effectdate record. In other words i want my outout like this..
Vehicle_Specialisation_Id Motortrade_Id Effectdate
A94ACDEE 007094C1 2006-05-16 13:01:00.000
A94ACDEE 007094C1 2006-06-05 12:19:00.000
A94ACDEE 007094C1 2006-06-12 17:37:00.000
A94ACDEE 007094C1 2006-07-25 09:44:00.000
A94ACDEE 007094C1 2006-08-01 12:05:00.000
1F3D7BDB E1A50F16 2006-05-22 12:00:00.000
1F3D7BDB E1A50F16 2006-07-14 15:05:00.000
1F3D7BDB E1A50F16 2006-08-21 12:59:00.000
I have wriiten a SP to do this one but it is a bit slow and i have quarter million records in my table. So i want a simple and single update query to do this one.
Cheers
Praveen
View 3 Replies
View Related
Jan 2, 2008
I am tring to execute the simple update query in query analyiser but after some seconds process got block by the same process(DEADLOCK).
query:
update tablename
set col1 = 0
Other info may help you:
Database had 3 different primary data files. In these files, first two files restricted file growth option is selected and there size is full. Only third file has space and option to grow.
Table containts total 50 columns and 15 milion rows.
Please help me to solve this ASAP.
Thanks in advance.
View 12 Replies
View Related
Mar 21, 2005
I'm converting an ASP system from using msaccess to SQL Server as the db engine, and I'm stumped on the following query
update
timecard
set
TcdDate = #3/18/05#
TcdDate is defined as a date/time type
It will not run with the date bracketed by # signs, and when I take them out, 1/1/1900 is stored in the dbs. Is there a different symbol to bracket the date with or should I be using a function to convert the date?
View 4 Replies
View Related
Nov 23, 1999
Hi..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.
View 1 Replies
View Related
Mar 16, 2004
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
View 4 Replies
View Related
Dec 11, 2006
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 Related
Feb 26, 2008
I 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
View 5 Replies
View Related
Jul 23, 2005
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 Related
Oct 4, 2007
Hi folks. I'm trying to update a single field in a database table with the input from a single text box, but I'm getting "Insert Error: Column name or number of supplied values does not match table definition". I know at least part of the problem is that I haven't specified the name of the table field, but I'm not sure where to do this. The table in question has many fields, but I only want to update one. The submit button is "sendMail", which calls UpdateDB.I'm learning and trying to limit myself to 1 idiotic question per week. Please have mercy. Thanks. Chrisprivate void UpdateDB(string cmd)
{
// point to connection string in web.config
string connectionString = "Data Source=localhost;Initial Catalog=databaseName;Integrated Security=True";
// create connection object; initialize; open
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);
try
{
connection.Open();
// create sqlcommand object and assign connection
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.Connection = connection;
command.CommandText = cmd;
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
protected void sendMail(object sender, EventArgs e)
{
string cmd = @"INSERT INTO tableName VALUES ('" + this.TextBox2.Text + "')";
UpdateDB(cmd);
// send smtp mail ... }
View 3 Replies
View Related
Oct 9, 2007
I have been trying to create a simple page to update my SQL Express database with some text boxes that are auto filled.
If here is the code if you have any help for me it would be great. Partial Class Secured_Default2
Inherits System.Web.UI.PageProtected Sub Accept_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Accept.Click
SqlDataSource3.Update()
End SubProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
AcceptDate.Text = System.DateTime.Now.ToStringAcceptedBy.Text = Request.ServerVariables.Get("Logon_User")
StatusBox.Text = "2"End Sub
End Class<%@ Page Language="VB" MasterPageFile="~/Secured/MasterPage.master" AutoEventWireup="false" CodeFile="TEST.aspx.vb" Inherits="Secured_TEST" title="Untitled Page" %>
<%@ Import Namespace="System.Data.SqlClient" %><%@ Import Namespace="System.Data" %>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<p>
<br />
Date:
<asp:TextBox ID="AcceptDate" runat="server" ></asp:TextBox> Accepted By:
<asp:TextBox ID="AcceptedByBox" runat="server" ></asp:TextBox><br />
Status:
<asp:TextBox ID="StatusBox" runat="server" Width="18px"></asp:TextBox>
<asp:Button ID="Accept" runat="server" CommandName="Update" Text="Accept Ticket" /><br />
<br />
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:HelpDeskConnectionString2 %>"
SelectCommand="SELECT TicketNUM, Accepted, AcceptedBy, Status FROM HelpDesk WHERE (TicketNUM = @TicketNUM)"
UpdateCommand="UPDATE HelpDesk SET Accepted = @UAccepted, AcceptedBy = @UAcceptedBy, Status = @UStatus WHERE (TicketNUM = @TicketNUM)">
<UpdateParameters>
<asp:FormParameter Name="UAccepted" FormField="AcceptedDate"/>
<asp:FormParameter Name="UAcceptedBy" FormField="AcceptedByBox" />
<asp:FormParameter Name="UStatus" FormField="StatusBox" />
<asp:QueryStringParameter Name="TicketNUM" QueryStringField="TicketNUM" />
</UpdateParameters>
<SelectParameters>
<asp:QueryStringParameter Name="TicketNUM" QueryStringField="TicketNUM" />
</SelectParameters>
</asp:SqlDataSource>
</p>
</asp:Content>
View 4 Replies
View Related
Jun 18, 2008
I am attempting to update contact data that is displayed in a form. Unfortunately the followiing code that runs off the button click event does not seem to work or I am misssing something to make it fire. I get no errors but the data does not get updated. What am I missing? Thanks.Private Sub btn_Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Submit.Click
Dim strConnection As StringDim cn As SqlConnection
Dim sql As String
strConnection = ConfigurationSettings.AppSettings("ConnectionString")cn = New SqlConnection(strConnection)
sql = "UPDATE vw_BS_IssuerContacts SET ContactLastName = @ContactLastName, ContactFirstName = @ContactFirstName, ContactTitle = @ContactTitle, ContactEmail = @ContactEmail, ContactPhone = @ContactPhone, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, Country = @Country, ZipCode = @zipCode, AccessLevel = @AccessLevel WHERE IssuerCode = @IssuerCode"Dim UpdateCommand As New SqlCommand(sql, cn)
UpdateCommand.Connection = cn
UpdateCommand.CommandText = sqlUpdateCommand.Parameters.Add(New SqlParameter("@IssuerCode", Lbl_IssuerCode.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@ContactLastName", txt_Lname.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ContactFirstName", txt_Fname.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@ContactTitle", txt_title.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ContactEmail", txt_Email.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@ContactPhone", txt_Phone.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Address1", txt_Address.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@Address2", txt_Address2.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Address3", txt_City.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@Country", txt_Country.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ZipCode", txt_zipCode.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@AccessLevel", Lbl_currAccessLevel.Text))
'UpdateCommand.Parameters.Add(New SqlParameter("@AccessLevel", SqlDbType.Char, 2).Value = txt_CurrAccessLevelSelectedValue))UpdateCommand.Parameters.Add(New SqlParameter("@UserName", txt_Username.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@infopw", txt_pw.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Company", txt_IssName.Text))
'do the update
cn.Open()
UpdateCommand.ExecuteNonQuery()
cn.Close()
End Sub
View 1 Replies
View Related
Jul 20, 2005
Hey all,i have a students_table which i want to do a multiple update on.Update student_tableset grant = 35000where course_id = 2There are lots of students in the student table which have course_id of2.I get this error:Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used as anexpression.The statement has been terminated.Does anyone know how to do a multiple update like this.It seems such asimple thing but im confused*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Mar 20, 2008
I've got two tables:
CallLog (one)
Repository (many)
They both have the shared id of CallID. I'm trying to write a trigger that when one adds a record to the repository table, I want the CallLog table flagged with 'T'. My existing code is flagging everything thing in the CallLog table that has CallID match from the Repository table... How do I update the single row in the CallLog table? Driving me nuts!
USE [HEATPROD]
GO
/****** Object: Trigger [dbo].[WriteAttachmentFlagNew] Script Date: 03/19/2008 12:29:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[WriteAttachmentFlagNew]
ON [dbo].[Repository]
AFTER Insert
AS
BEGIN
UPDATE CallLog
SET AttachmentFlagNew = 'T'
FROM repository
WHERE calllog.CallID= repository.CallID
END
View 6 Replies
View Related
Jul 10, 2006
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!
View 2 Replies
View Related
May 3, 2000
I have a simple update/initialization query (set integer column = 0 on all rows) that's been running for over 28 hours. There are just over 27 million rows in the table. In current activity it shows that the transaction is open but it's sleeping, and in locks it shows 1 DB S mode lock, 766 page X mode locks, 1 page U mode lock, and one table X mode lock. Server is 7.0 with 1.7 gig ram. Anyone have any ideas as to why it's taking so long? Table is about 7 gig in size; can't get to it in Enterprise Manager without locking it up...
View 3 Replies
View Related
Nov 19, 1999
Hi there,
I was trying to update records of a recordset(ADODB.Recordset) returned from a stored procedure(SQL server 7.0) in ASP file, this stored procedure select records into a temporary table, so the records returned by the SP actually physically are in tempdb database rather than in the user database.
When executing Update statement , I got the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Database name 'Mydatabase' ignored, referencing object in tempdb.
Any ideas/comments would be highly appreciated!
Dana Jian
dan_jian@hotmail.com
View 1 Replies
View Related
Feb 9, 2006
Hi i have three tables
T1 is a table of data imported by DTS, it has staff numbers and location names
T2 is a table that contains system data about staff and has a column for the location id
T3 is a table for the location names and their ID's
i am trying to write the sql to update T2 with the new location IDs for staff that have moved location but am not getting what i need.
so far i have :
update
T2
set T2.LocationId =T3.LocationID
from T3
inner join T1
on T3.departmentname = T1.contact_location
the update runs but sets all rows to the same value. am now burried deep in the [I've stared at this too long and cant see the wood for the trees] stage :mad:
Thanks
FatherJack
View 2 Replies
View Related
Aug 31, 2005
Kairn writes "I have created a table and imported data from another table to it. I need to update the existing data with values from another import but only need specific columns and am assuming a stored procedure is the way to go...IF I create a source table as well as destination(?). I have to compare the value in 6 columns to find the matching record in the destination, then update 5 of the other columns where source=dest. Finally, I need to sum, by row, the value in 3 of the columns and update another column to reflect it. All rows are unique in the destination. The source is a duplicate of them. Basically, the rows are identical aside from the values in the columns I want transferred. The rows must remain unique. I am a newbie and have no idea how to do this. Would you please submit a basic outline and I can fill in the rest?
Thank-you very much for your time and expertise,
Kairn"
View 1 Replies
View Related
Mar 4, 2008
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!
View 4 Replies
View Related
Jul 20, 2005
I am extremely new at SQL Server2000 and t-sql and I'm looking tocreate a simple trigger. For explanation sake, let's say I have 3columns in one table ... Col_1, Col_2 and Col_3. The data type forCol_1 and Col_2 are bit and Col_3 is char. I want to set a trigger onCol_2 to compare Col_1 to Col_2 when Col_2 is updated and if they'rethe same, set the value on Col_3 to "Completed". Can someone pleasehelp me?Thanks,Justin
View 7 Replies
View Related
Apr 29, 2008
hello,
I recently transfered date into my sql server source table from Access data base but at that time one column was blank. ID and mainID so mainID was missing
Now i received and excell sheet. in which again i have ID and mainID both provided .
please let me knwo how do i update those mainID into the sqlserver cource table column using available ID.
thank youi
View 3 Replies
View Related
Aug 29, 2007
I searched a bit but didn€™t get too far in actually solving a case of deadlock in a simple query I have running here. The queries in question are executed under 2 separate transactions (Serializable IsolationLevel) and are shown below. I guess I don€™t understand how those 2 can deadlock because they are operating on different rows of the table and Serializable should keep them isolated pretty well too. Is it because I€™m using the column value inside an update stmt? How should this query be split if that€™s the case?
This is what the SQL Profiler has to say:
Lock: Deadlock Chain Deadlock Chain SPID = 59
Lock: Deadlock Chain Deadlock Chain SPID = 57
Lock: Deadlock my_user_name
57: UPDATE CreditCard SET Balance = Balance - 200 WHERE (Account = 0 AND CardHolder = 'Foo' AND Balance - 200 >= 0)
59: UPDATE CreditCard SET Balance = Balance - 250 WHERE (Account = 3 AND CardHolder = 'Bar' AND Balance - 250 >= 0)
I also used DBCC TRACEON(1204, 3605, -1) but I don€™t understand what the SQL log is telling me. Can anyone shed some light on why the above 2 statements sometimes cause the following:
System.Data.SqlClient.SqlException: Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I really don't want to retry the update if I don't have to. Table looks like:
Column DataType Length
Account int 4
CardHolder char 64
Balance float 8
View 9 Replies
View Related
Oct 6, 2006
Following update runs 20 hours till TEMPDB grows up to 400GB and runs out of space with error message:
Msg 1105, Level 17, State 2, Line 8
Could not allocate space for object 'dbo.Large Object Storage System object: 440701391536128' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
T_PERF_LOC has 30,000,000 rows and T_MASTER_LOC has 2,000,000 rows
There is and index on V_KEY in both tables. CHANNEL_KEY is of Integer datatype and not indexed.
F_MAP function performs simple lookup on very small table (10 rows)
UPDATE A
SET CHANNEL_KEY = OTHERDB.DBO.F_MAP(b.ID)
FROM T_PERF A
JOIN dbo.T_MASTER b
on a.V_KEY = b.V_KEY
Any ideas why?
Thanks
View 4 Replies
View Related
Feb 9, 2007
Hi,
My scenario:
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
Thanks in advance for your help.
Regards,
$wapnil
View 4 Replies
View Related
Oct 1, 2007
I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>"
SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work. I get a "server tag is not well formed" error.
View 2 Replies
View Related
Jan 7, 2008
I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas
Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far: Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris
View 2 Replies
View Related
Mar 18, 2008
hi,i have three fields in the database tablep1 p2 rank10% 20% 3 21% 40% 2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh
View 2 Replies
View Related
Apr 21, 2005
Hi All
Can anyone tell me what this simple SQL query would be:
Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
Many thanks
accelerator
View 2 Replies
View Related
May 15, 2005
I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"
View 4 Replies
View Related
May 28, 2006
I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere. That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help. Here's what's going on:
I have a "Documents" table. Let's call it DI have a "Benefits" table. Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships. Let's call that table BD.
When I create a document it creates a row in D. Let's call that D1, D2...DN. When I create a benefit it creates a row in B. Let's call that B1, B2...BN
When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table. Now here's what my challenge is. Suppose I have these entries in my tables:
D: D1, D2, D3B: B1, B2, B3
Table BD:B1-D1B2-D1B2-D2
When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1. In this case, I want to show the user D2 and D3 but not D1 since that one's already added. How do I write a SQL query to do this?
Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3)
SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL)
Any thoughts?
View 2 Replies
View Related
