Complex Update Statement Not Working

Nov 6, 2006

hi.

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');



and, what is the solution? thank you.

View 8 Replies


ADVERTISEMENT

Please Help With Complex Update Statement Logic

Nov 8, 2006

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')

View 22 Replies View Related

HELP--UPDATE STATEMENT NOT WORKING

Mar 21, 2000

Hi,

I am trying to write an update query to update rows in one table.

Structure of Table1:

SocialSecurityNumber Varchar(9) -- Primary Key
Name Varchar(30)

Structure of Table2 :

SocialSecurityNumber Varchar(9)
Name Varchar(30)

Table 1 contains:

Row1: "123456789" Sally
Row2: "999999999" Bill
Row3: "333333333" Alex

Table 2 contains:

Row1: "123456789" <NULL>
Row2: "123456789" <NULL>

Basically I want to update the name column in Table 2 (based on the SocialSecurityNumber column) so that after
the update Table 2 will contain:

Row1: "123456789" Sally
Row2: "123456789" Sally
------------------------------------------------------------
First I tried:

UPDATE Table2
INNER JOIN Table1 ON Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
SET Table2.Name = Table1.Name
WHERE Table2.SocialSecurityNumber = Table1.SocialSecurityNumber

This works in Access but not it SQL Server 7.0.
------------------------------------------------------------
Then I tried:

UPDATE Table2
INNER JOIN Table1 ON Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
SET Table2.Name = Table1.Name

Again this works in Access but not it SQL Server 7.0.
------------------------------------------------------------
Finally I tried:

UPDATE Table2, Table1
SET Table2.Name = Table1.Name
WHERE Table2.SocialSecurityNumber = Table1.SocialSecurityNumber

This also did not work.
------------------------------------------------------------
Is there any way to write this update statement without cursors?

Thanks.

View 1 Replies View Related

Update Statement Not Working

Oct 2, 2007

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

Date.FiscalPeriodOfYear = Labor.FiscalPeriodOfYear

WHERE Labor.FiscalYear = CAST(SUBSTRING(DATE.Date_Code,1,4) AS NVARCHAR(50)) AND

Budget.FiscalYear = CAST(SUBSTRING(DATE.Date_Code,4,1) AS NVARCHAR(50)) AND

Date.FiscalWeekofYear = 1

View 6 Replies View Related

Update Statement Not Working

Apr 11, 2006

I've got the following update statement:

UPDATE ISSUE_ACTIONS
SET BAE_FLAG = 2
WHERE IA_ISSUE_NO = 399
AND IA_SEQUENCE = 20

The fields BAE_FLAG, IA_ISSUE_NO, and IA_SEQUENCE are all of the type int.

When I run this code inside of my windows app (C#),

cmd3.CommandText = "UPDATE ISSUE_ACTIONS " +
"SET BAE_FLAG = 2 " +
"WHERE IA_ISSUE_NO = 437 " +
"AND IA_SEQUENCE = 13";

try
{
cmd3.ExecuteNonQuery();
}
catch (Exception e)
{
throw (e);
}

I get a timeout error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.

But when I run the SAME statement from Query Analyzer, it executes without a problem.

Has anyone run into this issue before? How do I get around this?

The CommandTimeout property of cmd3 is set to the default because this is not a complex query and should not take more than .5 seconds to execute.

View 5 Replies View Related

(URgent) My Update Statement Is Not Working..

Dec 17, 2007

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

View 17 Replies View Related

T-SQL (SS2K8) :: Update With Case Statement Not Working?

May 29, 2014

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.

View 6 Replies View Related

My Update Statement Isn't Working But Select And Insert Are. What's Wrong?

Aug 11, 2007



here is my code:


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?

View 5 Replies View Related

Complex Join Replication Filtering Is Not Working In 3.5 Beta

Sep 11, 2007

Hi,

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.

View 5 Replies View Related

Please Help With Complex (for Me) Sql Statement

Sep 3, 2006

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]????

View 6 Replies View Related

Complex Sql Statement

Nov 13, 2006

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)
 
Insert into Transaction
(
Payee
,Deposit
,AccountNumber
)
Values
(
'Account Credit'
,@NetCommission
,@AccountNumber
)

View 13 Replies View Related

Complex Sql Statement, Need Help

Jan 15, 2004

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.

does anyone know how to fix this query?

View 9 Replies View Related

About The Complex SQL Statement

Mar 18, 2004

Belows are the data of table T1

field1 field2 field3 Value
F1 F2 F3 A
F1 F2 F3 B
F1 F2 F3 C
... .... .....
F1 F2 F3 Z


Can any bright person help to script the SQL to extract above data set and present as below ???

field1 field2 field3 Value
F1 F2 F3 ABC......Z

Thanks for help

View 2 Replies View Related

Complex Insert Statement

Oct 9, 2006

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

View 1 Replies View Related

Complex SELECT Statement Help

Aug 30, 2007

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.
 
Sincerely,

View 3 Replies View Related

Help Forming A Complex SQL Statement

Nov 12, 1998

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)

sqlFind = "([RouteID] = " & intRoute & " AND [City] = '" & strLocation & "' AND [StatusCode] = '" _
& strStatus & "' AND [Rail] = '" & strRail & "')"



rs.FindFirst sqlFind
If rs.NoMatch = False Then

If (rs!daymarker = "" Or IsNull(rs!daymarker)) And (IsNull(rs!daymarker) Or rs!cutoff = "") Then
IsOnTime = "Finished"
rs.Close
Exit Function
End If

dateScheduleDateTime = DateAdd("d", rs!daymarker, (dateIngateDate + rs!cutoff))

dateDifference = DateDiff("h", dateScheduleDateTime, (dateCurrentDate + dateCurrentTime))
If dateDifference <> 0 Then

IsOnTime = -dateDifference
End If
If dateDifference = 0 Then
IsOnTime = 1
End If
Else
IsOnTime = -9999
End If

rs.Close
End If
Exit Function

View 1 Replies View Related

Complex SQL SELECT Statement

Jul 23, 2004

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!!

View 4 Replies View Related

Please Help Me With A Complex DELETE Statement

Jan 27, 2005

Hello, currently I have a query like this:


PHP Code:




 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.

Can anyone help me please? Its kinda urgent.

Thansk!

View 5 Replies View Related

Complex SELECT Statement

Feb 24, 2004

TABLE : USER
USERID
1
2

TABLE : TIME
TIMEID|USERID|RT|OT|DOT|DATE
1|1|8|2|1|2004-02-01
2|1|8|2|0|2004-02-02

3|2|8|0|0|2004-02-01
4|2|8|2|2|2004-02-02

RT : Regular Time
OT : Over-Time
DOT : Double Over-Time

I need to write a query to display the results in this way

USERID|DATE|TIME
1|2004-02-01|8
1|2004-02-01|2
1|2004-02-01|1

1|2004-02-02|8
1|2004-02-02|2

2|2004-02-01|8
2|2004-02-02|8
2|2004-02-02|2
2|2004-02-02|2

basically, the time entries for each user each day , seperate rows for RT, OT, DOT if they are not equal to 0

ive been breaking my head on this for quite a while. any help is appreciated.

thanks

View 5 Replies View Related

Help: Complex Select Statement

Jul 20, 2005

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

View 1 Replies View Related

Complex SQL Select Statement That Works But...

Jul 24, 2006

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?
 
Thanks!

View 3 Replies View Related

Complex SELECT Statement Issue

Sep 30, 2007

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 

View 14 Replies View Related

Issue With A Complex Case Statement

Apr 29, 2008

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'.

Any help is greatly appreciated!!!

View 2 Replies View Related

Help With A Complex UPDATE Query

Nov 18, 2006

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

View 13 Replies View Related

Please HELP Complex Update Need Genius

Apr 6, 2008

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



1


1


1


1


1
cccc
1
4321




bc
bb
ba
bc
bb
ba



bc
bb
ba
dddd
2
9876



w


2


2


k


2
dddd
1
9876


i am appraiser the help
TNX

View 16 Replies View Related

Row_number Selecting From A Complex Select Statement

Sep 3, 2007

Hi,


Code Snippet


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?

View 4 Replies View Related

ASP Update Method Not Working After A MSDE To MSSQL 2005 Expess Update

Oct 20, 2006

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

W2003 + IIS6.0

Pls advice?

View 1 Replies View Related

ExecuteNonQuery - Add Working/Update Not Working

Jan 7, 2004

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)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"
cn.Close()

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.

Please I would appreciate any advice...

View 2 Replies View Related

Please Need Rescue- Complex Update Logic Swap

May 14, 2008

please need rescue- complex update logic
this is my table







1

2

3

4

5

EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44

fld444
fld5
fld55
fld555


















1111

A

B

C

7

8

9

G

H

I

J

K

L

M

N


















2222

N

M

L

K

J

I

H

G

F

E

D

C

B

A


















3333

1

2

3

A

B

C

C

E

Y

I

O

W

Y

P




















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








value_swap

=1

=2

=3

=4

=5

EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44

fld444
fld5
fld55
fld555


















1111

A

B

C

A

B

C

G

H

I

J

K

L

M

N


















2222

N

M

L

K

J

I

H

G

F

E

D

C

B

A


















3333

1

2

3

7

8

9

C

E

Y

I

O

W

Y

P



















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













=1

=2

=3

=4

=5

EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44

fld444
fld5
fld55
fld555


















1111

A

B

C

7

8

9

G

H

I

J

K

W

Y

P


















2222

N

M

L

K

J

I

H

G

F

E

D

C

B

A


















3333

1

2

3

A

B

C

C

E

Y

I

O

L

M

N















TNX FOR ALL THE HELP I GET IN THIS Forum

View 7 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

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

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

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

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

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

View 1 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

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







Copyrights 2005-15 www.BigResource.com, All rights reserved