Hi all, have a pretty simple query. but am new to workin in multiple tables. This statement shows
Items in table1 that exists in table2 that match a filter.
I would like to change this query to update the TOBEDELETED column to 'F' based on the same column and filter match.
SELECT ITEMS.ITEMNO, ITEMS.TOBEDELETED
FROM ITEMS
INNER JOIN QLOG
ON ITEMS.ITEMNO=QLOG.SOURCEID
WHERE QLOG.SOURCE= 'ITEMS'
Thanks all in advance
Hi everyone..i m new to this field.. can anyone explain me with simple example onhow to insert,update,select data from the sqldatabase? i m using vwd 2005 express edition along with sql express edition. plz explain the simple example with code (C#) including how to pass connection strings etc.thank you.jack.
View 6 Replies View RelatedConsider the below script
CREATE TABLE #TEMP(Id int,CreatedBy varchar(30),ModfiedBy varchar(30))
CREATE TABLE #TEMP2 (ID int,SearchedBy varchar(30))
INSERT INTO #TEMP VALUES(1,'James',NULL)
INSERT INTO #TEMP VALUES(1,'James','George')
INSERT INTO #TEMP VALUES(1,'James','Vikas')
INSERT INTO #TEMP2(ID) VALUES(1)
INSERT INTO #TEMP2(ID) VALUES(1)
INSERT INTO #TEMP2(ID) VALUES(1)
Now i want to get the result as
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
SELECT CASE WHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
But when i convert this select to update, i am missing something...
My update is
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE #TEMP2
SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
WHERE #TEMP2.ID=CTE.ID
Only the first record gets updated...
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TESTÂ TO Satish.
Hello everyone,
I have the following sql query which basically gives me 10 ramdon records in a table, what i want to do is update those records selected. i.e update the select query.I want to upddate a field in the 10 records returned by the query below. Thank you.
SELECT TOP (10) *FROM tblSiebelActivity1000WHERE (DATEPART(mm, dteActivityDueRT) = MONTH(GETDATE())) AND (DATEPART(dd, dteActivityDueRT) = DAY(GETDATE())) AND (txtServiceRegion = 'nj02')ORDER BY NEWID(), txtActivityNumber
I have two tables. I need to put the first character in column2 table 2 into column2 table 1 where there id's are the same. The table where the information is coming from(table2) is an int datatype and the table where it is going to(table1) is a char datatype. Any help is appreciated, thanks in advance.
View 1 Replies View RelatedI have a sales table that has a customer_number, salesman_number and sales data (like item, quantity, etc), there is a customer table that has the customer_number, name, address, etc. and salesman_number, with the passign of time salesmen come and go and the customer service department is in charge of modifying the customer table and assign the new salesman number to them. All the previous sales have to be reasigned to this new salesman even though it wasnt him the one that sold the items (I know, I know, thats the way the company requieres it, this is an old system). Now to my question:
I have this select query that gives me the rows from table sales that have to be modified:
SELECT C.sales_rep,S.salesrep,S.*
FROMSALES AS S
INNER JOIN customer AS C
ONC.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = S.site_code AND
C.cust_no = S.cust_noAND
C.cust_sffx = S.cust_sffxAND
C.division = S.divisionAND
C.sales_rep != S.salesrep
WHERES.month = 2AND
S.year = 2005
ORDER BYC.sales_rep
I just want to know if my approach to convert this query from an update to a select is ok:
UPDATE SALES AS S
SET S.salesrep = C.sales_rep
INNER JOIN customer AS C
ONC.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = S.site_code AND
C.cust_no = S.cust_noAND
C.cust_sffx = S.cust_sffxAND
C.division = S.divisionAND
C.sales_rep != S.salesrep
WHERES.month = 1AND
S.year = 2004
Thanks for your help
Luis Torres
Hi there.
I have two databases, one called PWALive and one called Portal-Live.
what I want to do is create an extract from Portal-Live of e-mail addresses and then use this date to update a table in PWALive.
Here are the relevant tables
Portal-Live
tblMUD(MUD_ID, MUD_clock, MUD_email ... )
PWALive
employee(employee_number, email_address ... )
Relationship: MUD_clock = employee_number
My sql statement for the extract from Portal-Live.tblMUD looks like this:
SELECTMUD_clock,
MUD_email
FROM tblMUD
WHEREMUD_email IS NOT NULL
AND MUD_clock IS NOT NULL
I want to then update the correct employee in PWALive.employee:
UPDATE employee
SET ... --Not entirely sure what to do here!
WHERE employee_number = MUD_clock
How can I do this in one sql sequence/is it possible?
As I understand it I can use CONNECT TO but Icant get that to work, let alone know how to link the update with the first sql statement.
Big thank you in advance
-GeorgeV
Hi
how can you update many fields from a select ? :
UPDATE table1 (a1,b1,c1) SELECT (a2,b2,c2) FROM table2 WHERE table1.id = table2.id
for MS SQL 2000/25000
thank you for helping
I have a select statement
SELECT ORDHFILE.OABL, ORDTFILE.ODLOTSEQ, ORDHFILE.OACUSTPO, ORDHFILE.OABLDATE, ORDHFILE.OAREGN, ORDHFILE.OASHIPDTMM, ORDHFILE.OASHIPDTDD, ORDHFILE.OASHIPDTYY, ORDHFILE.OASHIPVIA, ORDHFILE.OAFOB, ORDHFILE.OAATTN, CUSTFIL2.COBILLNUM, CUSTFIL2.COCNTRY, ORDHFILE.OASFNAME, ORDHFILE.OASFAD1, ORDHFILE.OASFAD2, ORDHFILE.OASFCITY, ORDHFILE.OASFST, ORDHFILE.OASFZIP, ORDHFILE.OASFZIP2, ORDHFILE.OASTNAME, ORDHFILE.OASTAD1, ORDHFILE.OASTAD2, ORDHFILE.OASTCITY, ORDHFILE.OASTST, ORDHFILE.OASTZIP, ORDHFILE.OADESC1, ORDHFILE.OAOPID, ORDTFILE.ODPNUM, PRODFIL2.PUPNAME1, PRODFIL2.PUPNAME2, ORDTFILE.ODQORD, ORDTFILE.ODMEAS, ORDTFILE.ODUM, ORDTFILE.ODCSC, ORDREM.OCCPREM1, ORDREM.OCCPREM2, ORDREM.OCCPREM3, ORDREM.OCCPREM4, ORDREM.OCCPREM5, ORDREM.OCCPREM6, ORDREM.OCCPREM7, ORDREM.OCCPREM8, ORDREM.OCCPREM9, ORDREM.OCCPREM10, ORDREM.OCCPREM11, ORDREM.OCCPREM12
FROM B108FFCC.CHEMPAXSNF.CUSTFIL2 CUSTFIL2, B108FFCC.CHEMPAXSNF.ORDHFILE ORDHFILE, B108FFCC.CHEMPAXSNF.ORDTFILE ORDTFILE, B108FFCC.CHEMPAXSNF.PRODFIL2 PRODFIL2, B108FFCC.CHEMPAXSNF.ORDREM ORDREM
WHERE (((ORDHFILE.OAWHS)='19') AND ((CUSTFIL2.COCNUM)=(ORDHFILE.OACNUM)) AND ((ORDTFILE.ODBL)=(ORDHFILE.OABL)) AND ((PRODFIL2.PUPNUM)=(ORDTFILE.ODPNUM)) AND ((ORDREM.OCBL)=(ORDHFILE.OABL)) AND ((ORDREM.OCPNUM)=(ORDTFILE.ODPNUM)) AND ((ORDREM.OCMEAS)=(ORDTFILE.ODMEAS)))
ORDER BY ORDHFILE.OABL, ORDTFILE.ODLOTSEQ
It works fine. However the data is being duplicated every 15 minutes instead of updating the changes. I am getting the same information over and over again.
I'm using DTS SQL Server Enterprise Manager.
Is it possible to update an entire column with data from another table? I have allot of information that is date sensative and would rather just update one column rather than havnig to change the date all the time. I have started this string and am stuck about what tp put after the SET string
UPDATE scf_yfeesycom
SET netrealpl =
SELECT netrealpl
FROM scf_pandl
Where date > 2006-01-31
I have a huge table with 407 columns and around 90 million records.
I need to run an update on 10 columns..
updates as case, trimming, replace.....
What do you guys suggest? Should I run select into or run an update query that runs all night.?
Thanks!
I need to update a table as follows:
Update item1, item2, item3, item4 in a table where column1="email_address" and column2 is ordered acending so the most recent entry for a specific user is updated.
column2 is a time stamp.
Pseudo code something like this:
UPDATE Table1 SET item1, item2, item3, item4 WHERE Table1 is TOP 1 ORDERED BY column2 ASC column1="email_address"
So i want to update only the most recent entry of a customer with a specific email address. I can't get the SQL command together that will do that.
I aint that bright so specific syntax always helps.
Thanks,
Bill
Hi there. My question is: for example i have a table Test:Id, pId, Name, Count. Is that possible to execute select query and update query in one query? It means to select all records where Name = 'somename' and update Count field of all selected records? Thanks!
View 10 Replies View RelatedHi,
I have an insert statement like:
INSERT INTO
table2(
field1
)
SELECT
field1
FROM
table1
What I would like to be able to do is then update a field in table1 with the new identity created by the insert in table2...is there a way I can do this with SQL?
More Info:
The select actually groups the information as to merge data together into table2.
Any ideas would be great!
Thanks,
CES
I need to Update a table with information from another table. Below is my psuedo code - need help with the syntax needed for Sql2000 server.
JOIN tblStateLoc ON tblCompanies.LocationID = tblStateLoc.LocationIDUPDATE tblCompaniesSET tblCompanies.StoreType = tblStateLoc.StoreTypeWHERE tblCompanies.LocationID = tblStateLoc.LocationID
Is there some sort of sql command where a tuple in a table has one of its cells updated depending on the value of a cell from another table. Please I would appreciate some help.
Thanks
In answering this question you could just tell me what you think would work rather than trying to test it.
The following query works correctly and returns rows with pairs of values repid and repid2. I need loop through a large table and update *its* repid2 with rm_repid2 for any rows where its repid value equals rm_repid but only for the pairs that result in the below query
SELECT
rm.repid AS rm_repid
,rm.repid2 AS rm_repid2
,rm.id2contact
,r.repid AS r_repid
,r.prim AS r_prim
FROM sfrep r JOIN sfrepmst rm ON r.repid=rm.repid2
WHERE rm.repid IN
(
'TEST01'
,'TEST02'
)
Returns
rm_repid rm_repid2 id2contact r_repid
-------- --------- ---------------------------------------- -------
TEST01 NEW01 John Smith NEW01
TEST02 NEW02 Ken Roberts NEW02
TIA
Doug
i want to select a data from a employee table and update that select data to attendlog1 table with match Eid
this is a employee data
EID----------bid
26478---------2
this is a attendlog data
EID-------------EBID
26478------------NULL
i want this type of result
EID-------------EBID
26478------------2
BID and EBID fields are change but values are same.
i am selecting the top 25 records from a table for processing. i would like to set the processstatus field to 3 as soon as i select the records.
is there a way to do this in one query?
Hi Friends,
I need a select query to identify how many records violate the basic dateformat, later on i update the junkdata with null.
Initially I have a database, in which one field(for Date) is given as Varchar, now as we know varchar accepts all types of data, when migrating the same data to another server, i am using Date as datatype for the new field and want to remove all other format's of data (Junk data) entered to that field and want to ratain only the general format i.e, MM/DD/YYYY or MM/DD/YYYY, this query should also support MM<=12, DD<=31
Regards,
Prasad K
How to Use select statement when using Update statement.
Eg: Update program_video set program_id=(select id from program where recordname='122'), stopnumber=stopnumber*-1 where stopnumber < 0
Thanks & Regards
Jagadeesh
I have a table with only one row to get unique ID-numbers. To get the nextID-number I have to increase the number in the row: first, get the numberfrom the database, and then do the update.Some ASP-code:Set rs = db.execute("SELECT id FROM tbl_id")id = rs("id")db.execute("UPDATE tbl_id SET id ="& id + 1 &"")This is not safe: two different sessions cat get the same ID.Is it possible to increase the value of id in tbl_id and get the value atthe same time? Or is there any other way?/matte
View 6 Replies View RelatedHI Guys,
I have two table t1 and t2
t1 has a,b,c columns(no primary key defined)
t2 has a,b columns (a is primary key)
I want to update the table t2 with follwin query
update t2 set b=(select avg(b) from t1 group by a) where a=(select a from t1 group by a)
Is it possible to update table of result of other query?
or give me other solution for that how can i do that thing...?
In Dot net 2.0 we change using SQLDataSource to Conect with SQLDB.
Now for My case ,the Select SQL is dynamic when differnece user and parameters to the page, So if I want to Update the data input by user,then I must give Update/insert/delelte SQL to SQLDatasource's InsertCommand /UpdateCommand/DeleteCommand .
How to Generate the Insert/update/delete command for the SQLDataSource ? as in dot net 1.1 can use SQLCommandBuilder to generate it,but SQLCommandBuilder just support DataAdeptor not for SQLDataSource, Could any body know how to do it when the SelectCommand is dynamic and need to update data back to DB after edit?
thanks a lot.
I have a table which I need to obtain data from but am having a problem with select statement.
Specifically, I have a table that has multiple records for a particular hostName where the name of the host is either a shortname (say "Larry") or a long name (say "Larry's").
I need to display only the long names (NOT THE SHORT NAME records with the similar hostName).
Select winsHostName, len(winsHostName) from winsData where winsClientIPAddress IN (SELECT winsClientIPAddress from winsData Group By winsClientIPAddress Having count(winsClientIpAddress) > 1) Order by winsHostName
Which returns data
Name Length
ATVDDR 6 ATVDDR1 7
This is a s far as I can get but,
Now, I need to list all remaining table fields based on the Name with the longer length and this is where I run into trouble.
The output should read per below with the remaining table fields which I cannot seem to extract with nested select statement
Name IP Addr Location
ATVDDR1 1.1.1.1 2ndFloor
I tried adding another GROUP BY by this gets me no where because I do not need to execute another aggregate in select statement. Maybe I need to use INNER JOIN
Please advise any assistance.
Hi AllI hope some1 with more experience of Sql Server 2000 Stored proc's can help me or point me to somewhere I can find the info cos at the moment it's doing my head in, I have spent 2 day,s so far trying to solve the following query, the problem is I do not understand the errors I am getting or why so here is the code that won't work
ALTER PROCEDURE dbo.UpdateStock
@OrderID intASSELECT (dbo.Products.Stock - dbo.OrderDetails.Quantity) AS NewStockFROM dbo.OrderDetails INNER JOIN dbo.Products ON dbo.OrderDetails.ProductID = dbo.Products.ProductIDWHERE (dbo.OrderDetails.OrderID = @OrderID) UPDATE dbo.Products SET dbo.Products.Stock = NewStockWhat I am trying to do is get the OrderDetails.Quantity form the OrderDetails table the UPDATE the Stock feild in the Products Table by deducting the OrderDetails.Quantity From the Products.Stock to give me the new stock levelWell it works to an extent in that if I do this to SET dbo.Products.Stock = 10 it will put 10 in the Stock feild but I am unabke to get the value of NewStock , which caluculates the correct value into the set statment I keep getting invalid colum name, I have tried any combination I can think of but I still get error of 1 sort or another.So if any1 could help as I am pulling my hair out with this 1So perhaps some1 could cast there eye over it pleaseThanks
I've got four pages with in the first page a insert, in the second a select, in the thirth a update and in the fourth a delete statement. First the values of a textbox will be inserted in the database, then the values will be shown in labels and than it is possible to edit or delete the values inserted. Every inserted item belonging to each other has one ID. The follwing values has a second ID etc.
How can I make that possible?? I think that I should pass the ID's between the pages so I'm sure that I edit or delete the values that I want. So insert value 1 in page 1, show with select value 1 in page 2, edit or delete value 1 in page 3 and 4.
Maybe I didn't explain it good enough for you, please tell me then!!
Thanks!!
Hello everybody.
I am attempting the following relatively simple SQL.
UPDATE Server.db.dbo.table1 SET Value = @Value
WHERE Id IN (SELECT Id FROM table2)
This update is taking up to 1 minute. However if I remove the select and replace with actual values the update is completed instantly ie
UPDATE Server.db.dbo.table1 SET Value = @Value
WHERE Id IN (id1, id2, id3)
The select is also instant if executed in isolation. But when these two statements are combined time taken is too long
Please note that the update is occurring on a different server and that table1 does contain update triggers.
Any ideas on why this is happening? Cheers!
I've got myself in a bit of a pickle and would appreciate some help. After hitting Google and blazing the SQL books online I find myself on the trusty devshed forum.
Here's the statement:
Code:
UPDATE enquiries SET enq_dbtype =
(SELECT d.prop_dbtype
FROM enquiries as a JOIN enquiryproperties as b ON a.enq_id = b.enq_id JOIN sqlcluster.prop_file_db.dbo.property as d ON b.prop_id = d.prop_id
WHERE enq_datetime BETWEEN '12/19/2006' AND '05/15/2007')
The issue:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
As you can see, the table JOINs in the SELECT mean that there are too many rows are returned. Any ideas how I can limit the number of rows returned? There can be multiple entries in the enquiryproperties table for each enquiries row. The SQL statement needs to be limited so only one row is returned for each enquiries entry.
GROUP BY and DISTINT won't help me.... any ideas.
THanks... Will - BuR
Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?
For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...
ALTER PROCEDURE u_sp_x
@semester int
AS
Select Semester@semester
From ThisTable
Just curious.
Thanks,
Steve Hanzelman
the following criteria.
i have the selection all done but am trying to figure out how to do the following:
if column4 < 0 then add column4 to column3, move 0 to column4;
if column3 < 0 then add column3 to column2, move 0 to column3;
if column2 < 0 then add column2 to column1, move 0 to column2;
add column3 to column4;
move column2 to column3;
move column1 to column2;
if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;
these are all numeric data types.
I need assigning a group of users read, write, update (not delete) permission on all user databases. is there any way to do so instead of creating role on each database separately.
View 2 Replies View Related