Newbie - How To Update Multiple Rows With Select Statment
Oct 25, 2004
Newbie to the SQL Server.
UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)
Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.
Immd help will be greatly appreciated.
Pam
View 8 Replies
ADVERTISEMENT
May 13, 2004
Any idea how to fix my code. I am getting this error message below....
Server: Msg 512, Level 16, State 1, Procedure TrigRetReqRecIDP1, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Set @intRowCount = (select count(*) from RequestRecords where REID = @REID)
While (select @REID from RequestRecords) != @intRowCount
Begin
select @intRRID = (select REID from RequestRecords where REID=@REID and RRStatus = 'PE')
Exec TrigAssignImpTaskNewP1 @intRRID, @REID
End
View 2 Replies
View Related
Aug 10, 2005
ok my data base has 4 columns id,fname,lname,email and 3 rows 1,2,3
I made a simple update form that accesses the data and displays all three rows with the data in them:
<cfquery name="QUIZ" datasource="test">
SELECT id,fname,lname,email
FROM info
order by id
/cfquery
<HTML>
<HEAD>
<TITLE>Update an Employee</TITLE>
</HEAD>
<BODY>
<H1>Update an Employee</H1>
<FORM ACTION="databaseupdater.cfm" METHOD="POST">
<cfoutput query="QUIZ">
<INPUT TYPE="hidden" NAME="id" VALUE="#id#">
<P>
First name:
<INPUT TYPE="text" NAME="fname" SIZE="15" MAXLENGTH="30" VALUE="#Trim(fname)#">
<BR>
Last name:
<INPUT TYPE="text" NAME="lname" SIZE="15" MAXLENGTH="30" VALUE="#Trim(lname)#">
<BR>
E-Mail:
<INPUT TYPE="text" NAME="email" SIZE="15" MAXLENGTH="30" VALUE="#Trim(email)#">
<P>
</cfoutput>
<INPUT TYPE="submit" VALUE="update">
<INPUT TYPE="reset" VALUE="Clear">
</FORM>
</BODY>
</HTML>
you can use this form to send the updated data to databaseupdater.cfm:
<CFQUERY
DATASOURCE="test"
>
UPDATE info
SET fname='#fname#',
lname='#lname#',
email='#email#'
</CFQUERY>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
Thank You
</body>
</html>
the problem Grieg RN is each column in the database gets updated with all 3 rows of data in each column in stead of seperating the data in its correct row and column. I don't know much about the problem im just starting out please help. Thanks
View 5 Replies
View Related
Jan 3, 2006
Hi all,
I need to write an SQL statement to update multiple row (all) as follws and can just not get it right please help.
I have two fileds - "Tilte" and "Title1" in my "notes" table.
I would like to run through the entire table and replace the information in "Title1" with that of "Title", also I need to change a character whilst doing so , ie change a "=" to a "-"
Example
Title - "Will is coming to town = Paul"
need to copy this to Title1 and it must change to...
Title1 - "Will is coming to town - Paul"
Running MS SQL2000 SP4
Help appriciated
Thanks
View 3 Replies
View Related
Apr 1, 2007
I have to update the domain name on our email database. Is there a simple update I can use to change firstname.secondname@olddomain.com to firstname.secondname@newdomain.com. I've got about 2800 records to update and I don't fancy either loosing them all by doing a dogy update query or doing it one at a time.
Any help would be greatly appreciated................
View 3 Replies
View Related
Nov 11, 2014
I want to update multiple rows in one table
DDL of 3 tables
CREATE TABLE [dbo].[appl](
[app_id] [numeric](9, 0) IDENTITY(1,1) NOT NULL)
CREATE TABLE [dbo].[appl_party](
[prty_id] [numeric](9, 0) NOT NULL,
[app_id] [numeric](9, 0) NOT NULL)
CREATE TABLE [dbo].[party](
[prty_id] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
[lockbyid] [char](8)
I want to update muliple rows in table "party" for column "lockbyid"
below is the update query with which i can only update one row but i need to update multiple rows in party table
update party set LOCKBYID ='abcd' where prty_id in
(select distinct prty_id from sappl_party where app_id in (Select appl.app_id
FROM appl INNER JOIN appl_party ON appl.app_id = appl_party.app_id where appl_party.prty_id=1234))
and LOCKBY_USR_ID is null
View 1 Replies
View Related
Jan 10, 2006
Hi,Does SQL support update to multiple rows where values coming from asub-query?e.ginsert into TABLE1select column1, column2, column3 from TABLE2This is perfectly valid, assumes TABLE1 has only three columns,column1, column2, column3.My question: Is there any way to UPDATE values to TABLE1 similarly?something likeupdate TABLE1 set column1= ?, column2= ? , column3= ?where ................ TABLE2...........OR is it that, sql allows only UPDATEs with one set of values to nrows.Can any one throw some light on this.-Thanks and Regards,Maymon.
View 3 Replies
View Related
Oct 12, 2007
Hi,
I am writting a bit of SQL that takes data from one table then inserts it into another one. There is a field that can be any value (and is usually null), but when I insert the value in the new table then I want to execute:
IF table.field>0 then tabl2.field='400'. In other words for every row in the selection that has a field that is greater than 0 then '400' will be put into the new table.
I am not sure if the IF stamement can loop through a number of rows and execute depending on the value of a field in that row??
Thanks
View 7 Replies
View Related
Jul 30, 2007
I know this isn't right but I'm trying to build a single query in PHP to re write the sortorder column starting at 0 and writing every row in order.
Code:
update categories set (sortorder=0 where catid=32), (sortorder=1 where catid=33),(sortorder=2 where catid=36) where userid=111
PHP Code:
$qt="update categories set ";
for($i=0;$i<$num;$i++){
$a=$i+1;
$qt.="sortorder=$i";
if($a<$num){
$qt.=", ";
}
}
$qt.=" where userid=111";
Using PHP I can amend the loop above to slot in a row I want so I can change the sort order.
unfortunately I'm not sure how to build such a query in mssql, can anyone help?
View 5 Replies
View Related
Jan 28, 2008
Hai
I want to update mutiple rows using single statement.
Gender
M
F
Now I want to update M as Male and F as Female in Gender Table using single Sql Statement.
Can anyone help me please
Thanks in Advance...
Suresh Kumar
View 16 Replies
View Related
Feb 27, 2008
Hi, I am new to to the usage of sql server.I have data in the below mentioned format in table called stock_transaction.
stocknumber transtype transsubtype balance
s01 in cust 100
s01 out cust 200
s01 in deal 300
s01 out cust 100
s02 in deal 200
s02 out cust 300
s02 in cust 100
s02 out cust 200
s02 in cust 300
I want to generate a report which has the sum of balances of each stock number that belong to a particular trans group like
(in,cust) (out,cust),(in,deal) as below where (in,cust) (out,cust),(in,deal) are temporary aliases only for displaying as shown below
stocknumber in,deal out,cust in,cust
s01 300 300 100
s02 200 500 400
I am using case statements, I am able to retrieve the data when selecting of single trans group like(in,cust) or (out,cust) by giving its condition but was unable to select all the details of a particular stock number as a single record .
View 7 Replies
View Related
Jul 17, 2007
Hello,
Anyone know how to create an update trigger where the primary key isn't fixed.
If the primary key change how can I tie together the Deleted and Inserted tables if more than one row is updated?
/Patrik
View 9 Replies
View Related
Apr 24, 2008
I have a Select Distinct myfield that returns multiple rows with same value for myfield when it should only one. Why is this happening?
View 4 Replies
View Related
Jul 26, 2013
CREATE TABLE [dbo].[x1](
[nomer] [varchar](15) NOT NULL,
[tgl] [datetime] NOT NULL,
CONSTRAINT [pk_x1] PRIMARY KEY CLUSTERED
(
[nomer] ASC,
[Code] ....
Result :
nomer tanggal
12013-07-28 00:00:00.000
12013-07-29 00:00:00.000
12013-07-30 00:00:00.000
I Want to make :
nomer tanggal
1 28,29,30
View 2 Replies
View Related
Mar 6, 2008
Hi there,
I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results.
Here is my basic structure:
Table: Profiles
Fields: Id, ProfileName
Table: Groups
Fields: Id, GroupName, ProfileId
I then need to return something like this:
ProfileName,Groups
"Joe Soap","Group1, Group2, Group3"
Does anyone know how this can be done?
Thanks!
View 7 Replies
View Related
Jul 16, 2015
We have control table which will be useful whether we need to start the job or not. If we are starting the Job we will make it to 1.
Below is the Table Structure.
Table Name    IN_USE_FG
CUST_D Â Â Â Â Â Â Â Â Â Â 0
PROD_D Â Â Â Â Â Â Â Â Â Â 0
GEO_D Â Â Â Â Â Â Â Â Â Â Â 0
DATE_D Â Â Â Â Â Â Â Â Â Â 0
Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package.Â
Now we are getting dead lock because we are using same table at a same time. Because we are updating different records.Â
View 6 Replies
View Related
Jul 4, 2004
Hi,
I have a table of users, a table of categories, and a many-to-many table linking users to categories.
My problem is that I want to select all the users with an extra column containing a comma-separated list of the categories they belong to.
Here is a stripped-down version of the table fields:
tbl_User
UserId, Email
tbl_Category
CatId, CatName
tbl_User_Category
UserId, CatId
I have tried using the coalesce function to build a string, but can only get this to work for one row at a time:
DECLARE @list nvarchar(100)
SELECT @list = COALESCE(@list + ', ', '') + CAST(CatId AS varchar(4))
FROM tbl_User_Category
WHERE UserId = @UserId
SELECT @list as List
Any ideas on how to add to this to get it to do each row in tbl_Page? Or am I attacking this from the wrong angle?????
Any help would be fantastic!
thanks,
Rob
View 4 Replies
View Related
Apr 2, 2014
I am using xml schema that is like this:
<DetailRows>
<DetailRow>
<MonthNumber></MonthNumber>
<Amount></Amount>
</DetailRow>
</DetailRows>If my variable contains following xml document as un-typed xml
[Code] ....
However, if I use a typed xml variable that is based on above schema, I cannot use OPENXML. What is the correct way of achieving same result with a typed xml doc? I am using SS2K5.
View 1 Replies
View Related
Jun 5, 2008
Hi,
I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today.
I'm not sure how to do this.
SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything.
I like to end up with a column containing the count of rows <= today
and a column with rows where date > today.
Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?
Thanks,
Marc
View 2 Replies
View Related
Apr 29, 2008
I know that this is legal sql: "SELECT 1 AS Blah"
I want to do something like this except for I need to select multiple rows each with a different value for Blah. The query needs to be legal to be passed to the SqlCommand.ExecuteReader Method. Is this possible?
View 3 Replies
View Related
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
Oct 6, 2015
I have a table like this :
ID Description ParentID Level
B.01 Parent 1 H
B.01.01 Parent 1.1 B.01 H
B.01.01.01 Parent 1.1.1 B.01.01 H
B.01.01.01.01 Detail 1 B.01.01.01 D
B.01.01.01.02 Detail 2 B.01.01.01 D
[Code] .....
That means, only select Level=H, and display the last record of H with concatenated the description of each rows delimited with comma.
View 2 Replies
View Related
Apr 7, 2015
I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:
Original:
Product|price|initial_date|final_date
A|20.50|2014-08-10|2014-10-01
B|50|2015-01-15|2015-02-20
Resulting view:
A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01
I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.
Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.
By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.
View 1 Replies
View Related
Oct 1, 2015
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[code]....
View 4 Replies
View Related
Aug 25, 2007
Hi,
I have an error for the following statement:-
DECLARE @MONTHNO char
DECLARE @DB CHAR (8)
DECLARE @CR CHAR (8)
DECLARE @batchno int
DECLARE @ACTIVEYEAR int
set @batchno = 5
set @ACTIVEYEAR = 2007
SET @MONTHNO = 2
SET @DB = 'DB_'+CONVERT(NCHAR(2),@MONTHNO)
SET @CR = 'CR_'+CONVERT(NCHAR(2),@MONTHNO)
exec('update bmaster set '
+@DB+'='+@DB+' mysursor.damt,'
+@CR+'='+@CR+' mysursor.camt from bmaster inner join (select transact.year,transact.acctno,'
+' sum(transact.camt) as camt,'
+' sum(transact.damt) as damt,'
+' sum(transact.ccamt) as ccamt,'
+' sum(transact.cdamt) as cdamt'
+' from transact where transact.batchno='+@batchno+ ' and transact.year='+@ACTIVEYEAR
+' group by transact.year,transact.acctno) as mysursor on bmaster.year=mysursor.year and bmaster.acctno=mysursor.acctno')
the error as follows:-
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'mysursor'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
thanks for your help.
View 2 Replies
View Related
Sep 26, 2006
OK I have my SSIS Dataflow behaving the way it should but how does one map to a table that needs an updatde to a table.
In this case it is an Oracle Table so the SQl would liik like this...
UPDATE PPS_PRINCIPALSSET NAME = @Variable2WHERE (PRINCIPAL_ID = @Variable1)
View 4 Replies
View Related
May 9, 2008
I cannot figure out how to update ufd column from this statment. I can select the columns I want to update but how can I change it to update them from the select?
SELECT ufd.* FROM Tbl uf
JOIN Received rr
ON uf.ID = rr.SSN AND uf.Id = 36
JOIN Tbl ufd
ON uf.S_Id = ufd.S_Id
WHERE uf.Col <> 'Employee' AND ufd.Id = 35
View 8 Replies
View Related
Nov 14, 2013
i have a table named masterlist wherein the columns are :
name-----age------sex
andrew---19-------male
trisha---23------female
and i have also another table which is namelist that is linked to the masterlist table.. after i search for the record andrew in the table namelist..i updated andrew as 25 and sex is female..now i want reset andrew's record, same to the records that andrew has in the table masterlist..
View 3 Replies
View Related
Sep 26, 2007
Hi guys,
Does anyone who how I update two tables with one UPDATE statment (i.e. joining them with an INNER JOIN). I'm using SQL Server.
These are going to be very large tables, so I'd rather not have to run an UPDATE twice as it's going to be s l o w...
Thank all
Dâi
View 1 Replies
View Related
Feb 12, 2014
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
View 6 Replies
View Related
Mar 20, 2007
Hello, Is there an SELECT statement to just return the last 100 row in my tables? I have about 500 rows in my tables and I only need the info on the last 100 rows.
Thanks
Steve
View 4 Replies
View Related
Oct 2, 2007
hello,
i have a page "Picture.aspx?PictureID=4"
i have a FormView witch shows details about that picture and uses a stored procedure with input parameter the "@PictureID" token from query string
the Pictures table has among other rows "PictureID", "UserID" - uniqueidentifier - from witch user the picture belongs to
i have a second FormView on the same page, witch should show "other pictures from the same user" and uses a Stored Procedure
how should i write that stored procedure...frist to take the UserID from the picture with PictureID=4, then to pass it as input parameter and select the pictures witch has as owner the user with that UserID, and if can be done, to avoid showing the PictureID=4 again
a solution should be to add at querry the UserID too, but i want to avoid that
any sugestion is welcomed, please help me
THANKS
View 5 Replies
View Related
Apr 20, 2007
Okay here is the deal. I need to take all data from tbl 1 and match it to data in 3 other tbls. I need to have it return everything back to me even if it is null....
IE tbl 1 I match the invoice_num to tbl2 site_id and then tbl2 marekt to tbl3 market. however even if tbl1 invoice_num dose not match tb2 site_id I still need to have it retun to a null value to the site_id. Here is what I have so far. This will return everything where the invoice_num and site_id match.
Code:
Select distinct t1.ID,t1.Deposit_date,t1.Ref_Num,t1.Company,t1.check_num,t1.Check_Date,t1.Check_Date,t1.Check_Total, t1.Individual_PMT,t1.Invoice_Num,t1.Invoice_Desc,
t2.site_id,t3.CompanyCode,t3.CostCenter
From( PMTK_tbl as t1
Left Join Leaseinfo as t2 on t2.site_id = t1.Invoice_Num)
inner Join CostCenters AS t3
on t2.market = t3.market and t2.market_region = t3.RegionCode
View 4 Replies
View Related