can somebody explain to me why the below
update fails to update one row and updates
the entire table?
Code:
UPDATE addlist
SET add_s = 1
WHERE EXISTS
(SELECT a.add_s, a.email_address, e.public_name
FROM add a, edit e
WHERE a.email_address = e.email_address
and a.add_email = 'mags23@rice.edu' and a.add_s = 0 and e.public_name = 'professor');
hi.I am having probelms with an update statement. every timei run it, "every" row updates, not just the one(s) intended.so, here is what i have. i have tried this with both AND and ORand neither seem to work.i dont know why this is elluding me, but i'd appreciate help with thesolution.thanks.UPDATE addSET add_s = 1WHERE add.add_status = 0 and add.add_email = 'mags23@rice.edu'or add_s in(SELECT a.add_sFROM add a, edit eWHERE a.email_address = e.email_addressand e.public_name = 'professor')
I have the following update statement, which when executed, updates zero rows. However, if I replace the first two lines with a SELECT * , I will get records. Can somebody tell me why?
UPDATE [DW_DatamartDB]. [dbo].[FactLaborDollars]
SET [LaborBudget_USD] = Week1
FROM [DW_StagingDB].[ETL].[Transform_FactLaborBudget] BUDGET JOIN
[DW_StagingDB].[ETL].[Transform_FactLaborDollars] LABOR ON
Labor.Location_Code = Budget.Location_Code
JOIN [DW_DatamartDB]. [dbo].[DimDate] DATE ON
Date.FiscalWeekOfPeriod = Labor.FiscalWeekOfPeriod AND
Hi.. I have inserted couple of data in a particular table which is as follows.. Portfolio Table PortId PlanId PortfolioName PorfolioDescription ClientPortolioId
771 17838 BALPORT NULL NULL
772 17838 HIGHGROW NULL NULL
773 17838 MODGROW NULL NULLMy FundDBF is as follows RowNumber FUND_ID f.ASSETDESC Import
20 BALPORT
Balanced True
21 MODGROW
Moderate Growth True
22 HIGHGROW
High Growth True and this is my Update statement UPDATE Statements..PlanPortfolio SET PlanId = pm.PlanId, PortfolioName = pm.FUND_ID, PortfolioDescription = pm.ASSETDESC FROM Statements..PlanPortfolio p Join ( SELECT DISTINCT p.PlanId, pd.FUND_ID, f.ASSETDESC ---pd.FUND_ID FROM PartDBF pd INNER JOIN Statements..ClientPlan p on pd.PLAN_NUM = p.ClientPlanId INNER JOIN FundDBF f on pd.FUND_ID = f.FUND_ID WHERE pd.Import = 1 AND NOT ( pd.FUND_ID IS NULL OR Len(pd.FUND_ID) = 0 OR pd.FUND_ID NOT IN ( SELECT PortfolioName FROM Statements..PlanPortfolio pp Where pp.PlanId = p.PlanId ) ) ) pm on p.PlanId = pm.PlanId
I am trying to put the above table with f,AssetDesc in the PorfolioDescription field.. Any help will be appreciated.. Regards Karen
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL THEN ves.VEmployeeID END FROM Employee E INNER JOIN VEmployeeStaging VES ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())
cn.Open()
Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()
adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" & textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)
cn.Close()
Response.Redirect("database.aspx")
it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?
I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:
SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30 After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.
I need some help on how to structure a sql statement. I am creating a membership directory and I need the stored procedure to output the Last Name, First Name (and if married) Spouse First Name. Like this Flinstone, Fred & Wilma All members are in one directory linked by two fields. [Family ID] all the family members have the same family id and then there is a Family position id that shows if they are the Husband, Wife or Kids. I have no problem with this part select (LastName + ',' + FirstName) as Name, [Phone 1] as Phone, [Unit Name] as WD, [Street 1] as Street, SUBSTRING(City,1,3) as City, SUBSTRING(Postal,1,5) as Zipfrom Membership Where [HH Order]=1 Order By LastName ASC Could someone help me on how to display the " & Spouse FirstName " as part of the name field only if there is a spouse [HH Order]=2 for the current [Family ID]????
I need to get multiple values for each row in a database, then do a calculation and insert the calculation and the accountnumber related to the calculation the data, into a different column. I get an error trying it this way...there is no real identifier, it is jsut something that needs to get done per row...any ideas on how I can accomplish this? Declare @NetCommission decimal Declare @AccountNumber varchar(50) Set @NetCommission = (select (CommissionRebate * Quantity) from Account A Join Trades T on A.AccountNumber = T.AccountNumber) Set @AccountNumber = (select A.AccountNumber from cmsAccount A Join Trades T on A.AccountNumber = T.AccountNumber)
i have a complex sql statement and i think that my structure looks good but apparently not because i keep getting the same error, i was wondering if anyone knew how to correct this problem.
SELECT A.*, B.Name, C.SIName, D.IID,
(Select [LastName] , [FirstName] FROM E INNER JOIN F ON E.SID = F.SID , A WHERE F.Emp='Service' AND E.Lead=1 AND E.ID=[A].[D]) AS Service,
(Select [LastName] , [FirstName] FROM E INNER JOIN F ON E.SID = F.SID ,A WHERE F.Emp='Industry' AND E.Lead=1 AND E.ID=[A].[D]) AS Industry
FROM A , B, C WHERE (1=1) AND B.SID = C.SID AND A.ID = B.ID
i always get this error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I have this stored procedure that returns a rowid, distance. It has a latitude, longitude, and range as inputs, it takes the latitude and longitude and computes a distance with every lat/long in a table PL_CustomerGeocode. Once that distance is computed it compares that distance with the range, and then returns the rowid, distance if the distance is <= range. I have the SELECT statement down, but now i just need to enter this information into a seperate table PL_Distance with (rowid, distance) as columns. The sql statement is as follows, and i cant figure out where the rowid part is an the distance part is: DECLARE @DegreesToRadians float SET @DegreesToRadians = Pi()/180 SELECT rowid, Cast(distance As numeric(9,3)) AS distance FROM (SELECT rowid, CASE WHEN @srcLat = geocodeLat And @srcLong = geocodeLong THEN 0.0 WHEN ABS(Arc) > 1 THEN 0.0 ELSE 3963.1 * 2 * asin(Power(Arc, 0.5)) END AS distance FROM (SELECT Power(sin(DLat/2),2) + cos(@srcLat*@DegreesToRadians)*cos(geocodeLat*@DegreesToRadians)*Power(sin(DLong/2),2) AS Arc, rowid,geocodeLat,geocodeLong FROM (SELECT @srcLong*@DegreesToRadians-geocodeLong*@DegreesToRadians AS DLong, @srcLat*@DegreesToRadians-geocodeLat*@DegreesToRadians AS DLat, rowid, geocodeLat, geocodeLong FROM dbo.PL_CustomerGeoCode) AS x) AS y) AS z WHERE distance <= @range
Hi All, My sql is a little rusty, i ve been trying to do few things but still no luck. I m trying to query some data in one column based on certain . Here is my puzzle: I have 7 tables: categories, characteristics, configs, rm_cat, rm_chars, rm_conf and rooms. And here are the details on these tables: - categories: {cat_id, cat_name} - characteristics: {char_id, char} - configs: {conf_id, conf} - rm_cat: {room_id, cat_id} - rm_chars: { room_id, char_id} - rm_conf: {room_id, conf_id} - room: {room_id. room_name}
I m trying to select a "room_name" based on a certain cat_id, char_id and conf_id and i don't know how to do this.
I have what is turning out to be a very complex T-SQL query to build.
I'm porting an App from Access to SQL Server... one of the Access queries used a function made in VBA to return a value.
For the life of me, I can't figure out how to make this work using only SQL Statements.
I'm not even sure how to even ask this. So here I go.. I've really simplified the SQL statement to help out with this. There are initially two tables.
Container and TraceRecord
Container is a table of Cargo Containers (the truck trailers you see on the highways) TraceRecord is a table of location records as the containers move from city to city on the railroad.
The Containers move on fixed routes (ie.. Long Beach to Chicago, Long Beach to New York, etc...).. in the Container table there is a field called Route which records which Route the container is moving on and is related to a table of routes which I'll get to later.
[This is the old Access query.. notice the IIF statement and the function call to "IsOnTime"] SELECT c.ContainerID, c.IngateDate, t.Location, t.Status, t.EventDate, t.EventTime IIf(IsNull(c.IngateDate) Or IsNull(t.Location),"No Ingate Rail Record Captured",IsOnTime(t.Location,t.Status,t.Rail,c.Ro ute,c.IngateDate,t.EventDate, t.EventTime)) AS RailSch, t.Rail FROM c Container LEFT JOIN t TraceRecord ON c.ContainerID = t.UnitNumber
For each route there is a scheduled travel plan.
Example.. when a container is taken to the BNSF railroad in Long Beach that is called an "Ingate" and is considered Day 0 (zero). As the container moves on the railroad from Long Beach to Chicago, it will pass through other cities, and the TraceRecord table will record where the container is and what time and day.
What I need to do is determine, based on the latest TraceRecord record, by how many hours is the Container "on time".
There is a routing table which lists the predefined travel path for each route.. listing the number of days and hours a container should be a certain place since the day the container was taken into the railroad at the origin "Ingate".
What the IsOnTime function did was take the arguments and do some math with the routing tables and find out how many hours a unit is or is not on time.
Here's a copy of the function from Access 97 using DAO. I don't know if any of this is going to make sense to anyone.. but I'm stuck and don't know what else to do.
Function IsOnTime(strLocation As String, strStatus As String, strRail As String, intRoute As Integer, _ dateIngateDate As Date, dateCurrentDate As Date, dateCurrentTime As Date) As Variant On Error GoTo errorh: Dim rs As Recordset 'Rail Schedule Recordset Dim db As Database 'Current Database Dim sqlFind As String 'Search String to find city transit time Dim dateScheduleDateTime As Date Dim dateDifference As Integer 'Hours difference between trace and schedule Dim varvar As Variant
If dateIngateDate = Null Then IsOnTime = -9999 Else Set db = CurrentDb() Set rs = db.OpenRecordset("tTransitTimeTable", dbOpenSnapshot)
There are 3 tables, VendorLists, Vendors, and Referrals.
VendorLists is a linking table. It has VendorListID, VendorID, and ListID fields.
Vendors is linked to VendorLists through the VendorID field (one to many)
Referrals is linked to VendorLists through VendorListID (one to many)
I'm given a value for ListID and have to pull records from both the Vendors and Referrals table (a referral is a description of a vendor, one to many).
I am able to do this with the following SQL select statement:
SELECT Referrals.Description, Vendors.Company FROM Referrals CROSS JOIN Vendors WHERE Referrals.VendorListID IN (SELECT VendorListID FROM VendorLists WHERE (ListID = lid)) AND (Vendors.VendorID IN (SELECT VendorID FROM VendorLists WHERE ListID = lid)) ORDER BY Vendors.VendorID
This pulls all the appropriate records and values that i need and orders them by the identifier for the vendor. However, I want to randomly order the vendors but still group them together by company, so, if the VendorID is 1 for "joe's crab shack" and 2 for "billy's ice cream shop", the above will always list joe's crab shack first and all it's referrals. i want to be able to randomly order the vendors, but still keep the referrals of those vendors grouped together so that when i iterate over them, they're grouped.
Does anyone have any idea how to do this? I'm stumped!!
SELECT * FROM relations INNER JOIN paths ON relations.path = paths.path_id WHERE (paths.links = '161') AND (relations.node1 = 162) OR (paths.links = '161') AND (relations.node2 = 162) OR (paths.links = '162') AND (relations.node1 = 161) OR (paths.links = '162') AND (relations.node2 = 161) OR (paths.links LIKE '162%') AND (relations.node1 = 161) OR (paths.links LIKE '%162') AND (relations.node2 = 161) OR (paths.links LIKE '161%') AND (relations.node1 = 162) OR (paths.links LIKE '%161') AND (relations.node2 = 162) OR (paths.links LIKE '%161;162%') OR (paths.links LIKE '%162;161%') ORDER BY relations.node1
Don't pay attention to the 161 and 162 things, is just test data, now my problem is that I want to transform that into a DELETE statement, but I can't find the right way to do it, so far I managed to do something like:
PHP Code:
DELETE relations FROM relations INNER JOIN paths ON relations.path = paths.path_id WHERE (paths.links = '161') AND (relations.node1 = 162) OR (paths.links = '161') AND (relations.node2 = 162) OR (paths.links = '162') AND (relations.node1 = 161) OR (paths.links = '162') AND (relations.node2 = 161) OR (paths.links LIKE '162%') AND (relations.node1 = 161) OR (paths.links LIKE '%162') AND (relations.node2 = 161) OR (paths.links LIKE '161%') AND (relations.node1 = 162) OR (paths.links LIKE '%161') AND (relations.node2 = 162) OR (paths.links LIKE '%161;162%') OR (paths.links LIKE '%162;161%')
But that would delete only from the relations table and not from the paths table. I need to delete from both tables.
Here is my SQL string:"SELECT to_ordnum, to_orddate," _& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ONDDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ONDOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from resultof outer select) AS Total" _& "FROM DDTORD WHERE to_trak2id IN (39, 40, 41) AND to_ordtype = 's'AND to_status = 'c' GROUP BY to_ordnum, to_orddate ORDER BY to_ordnumDESC"The outter Select statement returns various amounts of order numbersrepresented by 'to_ordnum' in the outer Select clause which has tomeet the critera in the outer WHERE clause. I would like to placethese numbers selected into the inner WHERE clause for the innerselect statement where DOMBOM2.b2_ordnum = ?the order selected byouter select statement.I have tried placing to_ordnum into that location but the SQL2000server does not process it.Any suggestions, ideas?Thank you,Brett
I have a pretty complex SQL statement that looks like this: SELECT aspnet_Employers.active, aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title AS Contact, SUM(aspnet_Employers.EmployeeCount) AS [# Emps], COUNT(aspnet_Signups.account) AS [# Email Addresses], COUNT(aspnet_ContactMe.username) AS [# Contact Me], COUNT(aspnet_AppsSubmitted.account) AS [# Apply Now]FROM aspnet_Employers LEFT OUTER JOIN aspnet_AppsSubmitted ON aspnet_Employers.UserName = aspnet_AppsSubmitted.account LEFT OUTER JOIN aspnet_ContactMe ON aspnet_Employers.UserName = aspnet_ContactMe.username LEFT OUTER JOIN aspnet_Signups ON aspnet_Employers.UserName = aspnet_Signups.accountGROUP BY aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title, aspnet_Employers.active It does work the way i want it, but the problem is, on my Gridview when i change the Employers accounts "Active" status either way, it changes the username field from the username of the account, to "null". Why does it do this? What would i change to prevent this from happening?
I may not be seeing the forest through the trees here but here goes. I've got a table of computer configurations with columns for cpu, ram, m/b, hdd, etc. The values in those columns are related to the id field in another table named parts. The parts table has columns, id, partnumber, description, and cost. What I want to do is be able to pull a record from the computer configurations table and instead of getting the integers in the cpu, ram, etc. fields I want to put the corresponding description field from the parts table. For example:I want this...id Name CPU RAM MB HDD ... 1 Fast Machine Fast CPU Big RAM Greate MB Huge HDDNOT this....id Name CPU RAM MB HDD ... 1 Fast Machine 1 3 2 7 Below is a screenshot of my actual table relationships.Thanks in advance
I am new to SQL server 2005. I run a large query daily against a teradata warehouse and it populates an access database. I am now attempting to run my query in a SQL 2005 database. I get an error on this case statement:
CASE WHENCAST ( ( B.zip_cd ( format '99999' ) ) AS char ( 5 ) ) = '*****' THEN SUBSTR ( CAST ( ( B.zip_cd ( format '999999999' ) ) AS char ( 9 ) ), 1, 5 ) ELSECAST ( ( B.zip_cd ( format '99999' ) ) AS char ( 5 ) ) ENDAS ZIP_CD
The error is: Msg 102, Level 15, State 1, Line 229 Incorrect syntax near '99999'.
Well, I think it's complex anyway -- you might not :)TableDef:CREATE TABLE CustTransactions (TransactionKey int IDENTITY(1,1) NOT NULL,CustomerID int,AmountSpent float,CustSelected bit default 0);TransactionKey is the primary key, CustomerID and AmountSpent are bothindexed (non unique).What I would like to do is, for all of the records in descending orderof "AmountSpent" where "CustSelected = TRUE", set CustSelected to FALSEsuch that the sum of all the AmountSpent records with CustSelected =TRUE is no greater than a specified amount (say $50,000).What I'm doing at the moment is a "SELECT * FROM CustTransactions WHERECustSelected = TRUE ORDER BY AmountSpent;", programatically loopingthrough all the records until AmountSpent 50000, then continuine toloop through the remainder of the records setting CustSelected = FALSE.This does exactly what I want but is slow and inefficient. I am sure itcould be done in a single SQL statement with subqueries, but I lack theknowledge and experience to figure out how.The closest I can get is:-UPDATE CustTransactions SET CustSelected = FALSEWHERE (CustSelected = TRUE)AND TransactionKey NOT IN(SELECT TOP 50000 TransactionKey FROM CustTransactions WHERE(((CustTransactions.CustSelected)=TRUE))ORDER BY AmountSpect DESC, TransactionKey ASC);However, this mereley ensures only the top 50,000 customers by amountspent remain "selected", not the top "X" customers whose total spendis $50,000. I really need to replace the "SELECT TOP 50000" with someform of "SELECT TOP (X rows until sum(AmountSpent) =50000)".Is it even possible to achieve what I'm trying to do?Thanks in advance for any assistance offered!--SlowerThanYou
please HELP complex update need genius the table before the UPDATE
f555 f55 f5 f444 f44 f4 f333 f33 f3 f22 f22 f2 f11 f11 f1 fname val id
aaaa 2 1234
2
4
3
1
2 aaaa 1 1234
bbbb 2 9998
z
x
4
z
1 bbbb 1 9998
cccc 2 4321
1
1
1
1
1 cccc 1 4321
dddd 2 9876
w
2
2
k
2 dddd 1 9876
the table after the UPDATE (i need to update BY the ID of the Employee) i need to update only the numbers if the value =1 then the upper fields =aa ab ac if the value =2 then the upper fields =ba bb bc if the value =3 then the upper fields =ca cb cc if the value =4 then the upper fields =da db dc evry employee have 2 ROWS val 1 and val 2 and the table i ORDER BY ID,fname,val
f555 f55 f5 f444 f44 f4 f333 f33 f3 f22 f22 f2 f11 f11 f1 fname val id
bc bb ba dc db da cc cb ca ac ab aa bc bb ba aaaa 2 1234
2
4
3
1
2 aaaa 1 1234
dc db da
ac ab aa bbbb 2 9998
z
x
4
z
1 bbbb 1 9998
ac ab aa ac ab aa ac ab aa ac ab aa ac ab aa cccc 2 4321
This is difficult to explain in words, but the following code outlines what I am trying to do:
with myTableWithRowNum as ( select 'row' = row_number() over (order by insertdate desc), myValue from ( select table1Id As myValue from myTable1 union select table2Id As myValue from myTable2 ) )
select * from myTableWithRowNum Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID) objRS.Open strSQL1, objConn , 2 , 3 , adCmdText If not (objRS.BOF and objRS.EOF) Then objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1 objRS.update End If objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID) objConn.Execute strSQL1
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
i need to update for example the eployee 1111 with employee 3333 but with swap ( take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333 in field- fld2,fld22,fld222 )
Code Snippet ---update eployee 1111 with employee 3333 -so if i put the value 2 than ------------------ swap value between 2 employee set empid1= 1111 set empid2=3333 value_swap=2 if value_swap=2 than update fld2,fld22,fld222 with fld2,fld22,fld222 ------------------- take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333 --------------------in field- fld2,fld22,fld222
Code Snippet ---update eployee 2222 with employee 1111 -so if i put the value 5 than ------------------ swap value between 2 employees set empid1= 1111 set empid2=2222 value_swap=5 if value_swap=5 than update fld5,fld55,fld555 with fld5,fld55,fld555 ------------------- take the value of employee 1111 in field- fld5,fld55,fld555 and swap value between employee 3333 --------------------in field- fld5,fld55,fld555
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
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.
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