Update Select?

Apr 24, 2007

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         tblSiebelActivity1000
WHERE     (DATEPART(mm, dteActivityDueRT) = MONTH(GETDATE())) AND (DATEPART(dd, dteActivityDueRT) = DAY(GETDATE())) AND (txtServiceRegion = 'nj02')
ORDER BY NEWID(), txtActivityNumber

View 6 Replies

Update Select

Jul 3, 2006

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 Related

From Select To Update

Mar 4, 2005

I 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

View 6 Replies View Related

Select From One DB - Update In Another

Jan 25, 2007

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

View 14 Replies View Related

Update From Select

Oct 25, 2007

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

View 3 Replies View Related

Select And Then Update

May 19, 2008

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.

View 6 Replies View Related

Select/Update

Jun 29, 2006

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

View 2 Replies View Related

Select Into Or Update

Mar 25, 2008

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!

View 5 Replies View Related

Need Update Of Select Using TOP 1

Sep 21, 2006

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

View 1 Replies View Related

Select/update

Feb 28, 2008

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 Related

SELECT INTO And The UPDATE...

Apr 4, 2008

Hi,

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

View 12 Replies View Related

SELECT, JOIN And UPDATE

May 30, 2007

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

View 2 Replies View Related

Update And Select From Two Tables.

Mar 10, 2008

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

View 1 Replies View Related

Need To Convert This Select Into An Update

Sep 18, 2001

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

View 1 Replies View Related

Select And Update Query?

Nov 6, 2013

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.

View 2 Replies View Related

Got The Data With Select, How To Update?

May 13, 2007

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

View 4 Replies View Related

Select And Update In Same Query

Oct 26, 2007

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?

View 17 Replies View Related

Select Query And Update

Nov 20, 2007

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

View 2 Replies View Related

How To Use Select Statement During Update

Feb 8, 2008

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

View 7 Replies View Related

SELECT And UPDATE In The Same Query

Jul 20, 2005

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 Related

Update Query With Select

May 5, 2008

HI 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...?

View 1 Replies View Related

How To Update When Dynamic Select SQL By Using SQLDatasouce In .net 2.0?

Apr 5, 2007

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.

View 4 Replies View Related

Select And UPDATE Statement Help! (Using SQL Server)

May 7, 2007

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.

View 2 Replies View Related

Using The Result Of A SELECT As A Vaule To UPDATE With

Jun 13, 2005

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

View 2 Replies View Related

Insert, Select, Update And Delete

Apr 7, 2006

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

View 3 Replies View Related

Across Server Update And Select Issues

May 10, 2005

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!

View 1 Replies View Related

Select Update Join Issue

Jul 31, 2007

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

View 1 Replies View Related

Dynamic Select/Update Statement Possible?

Dec 3, 2004

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

View 6 Replies View Related

How To Do Update Of Select Columns Based On...

Jun 21, 2007

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.

View 7 Replies View Related

SQL 2012 :: SELECT / UPDATE On All Databases

Nov 20, 2014

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

Update Set Select Case When Statement

Nov 15, 2013

Update ed_abcdeeh set category = case when name_of_school = '' then category = 'No Facility' else '' end,status = case when name_of_school = '' then status = 'Non-Compliant' else 'Compliant' end.

How to make this query right.. when name of school is blank i want to update my category to No facility, but if the name of school has data it will just make it blank. same to the status..

VFP9.0 via MySQL 5.0

View 5 Replies View Related

Update From A Select With Multiple Joins

Feb 1, 2014

I am trying to do a query like this...

UPDATE g
SET g.GroupID = gp.GroupID, g.Contact1 = members.FirstName, g.BusPhone1 = members.BusPhone, g.HomePhone1 = members.HomePhone, g.Internet1 = members.Email
FROM statelst AS g INNER JOIN
grpcon AS gp ON g.GroupID = gp.GroupID INNER JOIN
members ON gp.MemberID = members.MemberID CROSS JOIN

I have my table statelst that I want to update certain columns from the values returned by a select on the grpcon table joined to the members table.I am getting an error "Incorrect syntex near 'JOIN'.

View 1 Replies View Related

UPDATE Inside A SELECT Statement?

Mar 24, 2015

I would like to UPDATE a column form my SELECT statement below but not sure how to go about this?!

The column I need to update is: courses.active = N

SELECT schools.id, schools.name, schools.cactus_name, schools.cactus_name_english, schools.address1, schools.address2, schools.city, schools.county, schools.postcode, schools.country, schools.active, schools.latitude, schools.longitude, schools.contact, schools.website, schools.email, schools.telephone, schools.fax, schools.dos, schools.other_contacts, schools.school_commission, schools.bo_notes, courses.name, courses.domains,

[Code] ....

View 7 Replies View Related