Trying To Update Multiple Row

Jan 30, 2007

Hi.

I am trying to update multiple row with different value. Is this possible w/ query or should I create a procecure?

Suppose I have two table A and B, I need to update values of A_b = B_b, A_c = B_c where A_a = B_a

below query somewhat shows what I am trying to do...

UPDATE A SET A_b = B_b, A_c = B_c WHERE A_a IN (SELECT B_a FROM B)



Thank you.

View 3 Replies


ADVERTISEMENT

SQL Server 2008 :: How To Update Multiple Column With Multiple Condition

Feb 25, 2015

I need to update multiple columns in a table with multiple condition.

For example, this is my Query

update Table1
set weight= d.weight,
stateweight=d.stateweight,
overallweight=d.overallweight
from
(select * from table2)d
where table1.state=d.state and
table1.month=d.month and
table1.year=d.year

If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column

I can't write an update query for each condition separately because its a huge select

View 7 Replies View Related

Plz Help...update Value In Multiple Db Table Using Single 'update Command'

Mar 18, 2005

hi,friends

we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------

i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.

i want to update value in multiple database table(more than one) using single 'update command'

i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.

it's urgent.

thanks in advance.

View 2 Replies View Related

Is There A Way To Update Multiple Fields Using UPDATE Command

Oct 19, 2005

UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?

View 1 Replies View Related

Multiple Update With Multiple Condition

Jul 20, 2005

I'm having an Employee table with a Salary field. How can we increate thesalary of the employees with following conditions:1) salary between 1000 and 10000 : increase 25%2) salary between 10000 and 20000 : increase 15%3) salary between 20000 and 30000 : increase 5%Surely you can create a cursor to solve this. But the question is, Is itpossible to solve this in a single query, if no what is most optimizedway?

View 4 Replies View Related

Multiple Row Update

Jun 9, 2008

Hi everyone, ok so heres my problem. I'm currently writting some scrips to query an simple inventory database. heres what i'm trying to do.

I have a .asp webpage that has a query on it that can dispaly everything in the inventory... old and new in a big html table.
The idea is to not actualy delete anything because i would still like to have a record of it's existance but at the same time i dont want it to clutter my "current" inventory i guess it would call it.

I added another column named deleted which is set to the Yes/No and TRUE/FALSE data types, i also put in a check box at the end of every row on the webpage, the idea here is that if a box is checked it will record that row's id via address bar/Get method and then when i hit submit it will run the update query for those rows checked

i changed my inventory query so that if an item's deleted column is checked to TRUE then it will not be displayed, so showing only the non deleted items.

UPDATE Licenses SET Licenses.deleted = TRUE
WHERE Licenses.LicensesID=33;

thats what i'm using to change it case by case basis, but what i would like to do is somthing like

UPDATE Licenses SET Licenses.deleted = TRUE
WHERE Licenses.LicensesID=33,34,67....so on

LicensesID is just a auto incrememted field jsut to give a unique value to a license's row..

does anyone know any tricks to accomplish what i'm trying to do? So far I havn't really found any satisfactory examples online. and i dont really want to completly change the structure of everything unless someone suggust somthing that is clearly a better way of doing things

thanks,
O

View 2 Replies View Related

Update Multiple At Once In Database

Apr 7, 2008

hi all~i very confuse~i using mssqlserver 2005 in visual basic 2005express..but i feel like want to update two table at onces..i faced some problem~i give an example..my tableA got user id, user name and table B got user id and status.In both table, i put user id as primary key and it will auto link each other  and i retrieve the user id and user name from table A and user status from table B~..in order to come out the data in gridview with three columns which are user id, user name and user status..in this case, it works perfectly..but when come to  update part, i only able to update one table at once, when i trying to update both table, it occurs errors...i did read some forum, some said it is impossible to do it?isn't?then is dere got any other ways to make me update both table at once?my purpose of this is wish to update user id and user name from table A and user status on table B....~i guess maybe my database design concept got problem..hope someone giv help on these... by the way izit foreign key can solve this?and how to put the foregin key?

View 4 Replies View Related

Update Multiple Rows

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

Multiple Row Update/Insert

Jun 29, 2005

Hi,I have a a table that is primarily a linking table that contains values for categories indid int             Indicator ID indtype int         Indicator Type can be either 0 or 1catflagnum int     Category Flag Number   the number of the Category catflagvalue int    Cat Flag Value    The Value for that category this table can then be updated from a web form.The Question I have is that can I do this in one statement or do I have to do it one at a time   i.e    The Data set could look something like          indid = 3                                       'This value will be the same for all rows         indtype = 0                                   'This will be the Same for all rows         Catflagnum = 1                             'This value will change         Catflagvalue = 1                           'This value will change            indid = 3                                       'This value will be the same for all rows         indtype = 0                                   'This will be the Same for all rows         Catflagnum = 2                            'This value will change         Catflagvalue = 3                           'This value will change          indid = 3                                       'This value will be the same for all rows         indtype = 0                                   'This will be the Same for all rows         Catflagnum = 3                             'This value will change         Catflagvalue = 1                           'This value will change            indid = 3                                       'This value will be the same for all rows         indtype = 0                                   'This will be the Same for all rows         Catflagnum = 4                            'This value will change         Catflagvalue = 5                           'This value will change A further complication is that in the table an entry may not be in that table for the category so we would possibly have to check if the record exist or do an insertI am stumped on this one?

View 5 Replies View Related

Multiple Update Statement

Nov 14, 2000

If I want to update 2 columns at 1 time with a where clause
like

update table a
set column1 = 8 and
set column2 = 9
where id = 10

I know you can't use the and statement, what is the correct syntax?

View 1 Replies View Related

Trigger For Multiple Db Update

Oct 30, 2003

I have a messy design that I can't change but need to keep in synch. I have a master table where a person's info is entered into. Upon that entry I have to take that info (name, bdate,ssn,location) and populate it to 4 separate tables in 4 separate databases, all of which have their own id field which has to be incremented (no none of them are identity columns). At the end of the process I have to update the master record with the id fields of all the other four tables. I was going to do this with an insert trigger. As far as protecting the id's across all four databases, I was going to use a begin trans and get the next id field for all four databases and then apply the logic to add the records. Does this seem a safe approach, or am I missing something? Do I need to do a begin tran for each database separately?
THanks

View 1 Replies View Related

Multiple Update Triggers

Oct 17, 2001

What would be the best way to handle different updates for a table, multiple triggers, or just one large triggger? I am not worried about their order of firing, just that they fire

View 1 Replies View Related

Update With Multiple Values

Apr 22, 2008

I have a table say #temp1 with coulmn Id and Description
and I do have another table #temp2 with column id and Projectdescription
but in #temp2 there could be more then one value against one id
like the data in #temp2 is look like

id Projectdescription
1 Computer project
1 update in computer project
1 another update in computer
2 Physics project
2 another update

but #temp1 has only one accurance of id
and data initially looks like
id Description
1 NUll
2 NUll
and I would like to update this table description from #temp2 Projectdescription column so that
data in #temp1 table look like after update

id Description
1 Computer project,update in computer project, another update in computer

2 Physics project, another update

I mean I would like to have concatination form of Projectdescription in description column against specific ID
I can achieve this by using UDF but i dont want to use that I just want to do it by update statement not even by using cursor

View 8 Replies View Related

How To Update Multiple Rows At Once

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

Update Multiple Rows - Not Getting It Right

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

Update From Multiple Tables

May 7, 2006

Given the below table relationships, I am trying to update the price of disk by %20 percent that were interpreted by 'Joe Smith'. I took a crack at this, but I do not feel comfortable with it. Do I need to do some type of join or union?

UPDATE Disk
SET price = price *.20
WHERE Interpreter.name = ‘Joe Smith;


MusicalWork (idwork, title)
Piece (idpiece, duration, iddisk, idwork)
Disk (iddisk, brand, type, issuingdate, price)
Execute (idpiece, idinterpreter)
Interpreter (idinterpreter, name, address)

View 3 Replies View Related

Update Multiple Rows

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

Update From Multiple Tables

Mar 28, 2008

I have a mess:

Table1(T1) contains Column1 (C1) which must be appended to Column2 (C2) in Table2 (T2) where an identifying code column(ID) matches in each table. An example of what I want is, where T1.C1 = 'Aluminum' and T2.C2 = 'Material', I would like it to be updated to T2.C2 = 'Material: Aluminum'. I tried this:

UPDATE T2
SET T2.C2 =
(SELECT T1.C1
FROM T1
WHERE T1.ID = T2.ID) + ':' + T2.C2

This did not work out. How should I fix this?

View 6 Replies View Related

SQL Update On Multiple Tables

Jul 20, 2005

Hi,I tried to use the following query to update a value in one table witha value from another table:UPDATE tbl1SET col1 = tbl2.col2FROM tbl1, tbl2WHERE tbl1.[id] = tbl2.[id]but it won't work. I also tried this with a subquery using "TOP 1",but that wouldn't work either. SQL Server 2000 gives me the followingerror-message:"Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated."What is wrong? It seems that the join is not functioning properly, butit functions properly with a rather 'simple' SELECT. How to updatethose fields, other than by hand ;)Regards,Falco Vermeer

View 3 Replies View Related

CTE With Multiple Update Statements?

Sep 19, 2006

HI Gurus

I have written one CTE (common table expression) and trying to use same CTE with three seperate UPDATE statements which gives me error saying "Invalid Object name" (it works fine when I try to use with 1 update statement (any one from three update statements)



Isnt it possible that I can use 1 CTE with mutiple update statements?



waiting for your reply....

View 4 Replies View Related

Multiple SET/WHERE Within Single UPDATE?

Mar 21, 2008



Can an Update statement support multiple SET/WHERE clauses? The following example won't work, but this is what I'd like to be able to do:





Code Snippet
UPDATE [CPSAgacar].tmp_CTA2

SET [Admit Date] = NULL
WHERE [CPSAgacar].tmp_CTA2.CalcAdmitDate IS NOT NULL

SET CalcDischDate = Discharge
WHERE [CPSAgacar].tmp_CTA2.CalcAdmitDate IS NOT NULL AND CPSAgacar].tmp_CTA2.CalcDischDate IS NULL






Thanks.

View 1 Replies View Related

Multiple Update, Several Where Criteria

Nov 2, 2007



Im sorry if this has been covered, I tried a search but I couldnt seem to find what I was after.

Anyway, I need an Update procedure which is actually several bunched into one. Ive had a stab at it myself, and perhaps my pseudo SQL might explain what I need..




Code Block
UPDATE [TW].[dbo].[TBLSalesPart]
SET

CASE WHEN [Part] = 'MONTV-' AND [YN] = 'False' THEN [SubCategory] = 20440 END
CASE WHEN [Part] = 'TC-' AND [YN] = 'False' THEN [SubCategory] = 20444 END




Hopefully this makes sense, cheers..

View 5 Replies View Related

How To Update Multiple Tables With Updatecommand

Jun 5, 2007

 hi all,i'm working on a formview which will update 2 tables which are linked together but..i'm getting an error of the control not being found.my code is as shown below                           <asp:SqlDataSource ID="invDetSrc" runat="server"                                                ConnectionString="<%$ ConnectionStrings:rockwellConnectionString %>"                                               ProviderName="<%$ ConnectionStrings:rockwellConnectionString.ProviderName %>"                                               SelectCommand="SELECT rockwell.logix_asset_list.Inventory_ID, rockwell.vendor.Vendor_Name, rockwell.logix_asset_list_category.Category_Name, rockwell.logix_asset_list.Total_Quantity, rockwell.logix_asset_list.Quantity_In_Stock, rockwell.logix_asset_list.Part_Number, rockwell.logix_asset_list.On_Order, rockwell.logix_asset_list.ImageUrl, rockwell.logix_asset_list.DrawingUrl, rockwell.logix_asset_list.Description FROM rockwell.logix_asset_list, rockwell.logix_asset_list_category, rockwell.vendor WHERE rockwell.logix_asset_list.Logix_Asset_List_Category_ID = rockwell.logix_asset_list_category.Logix_Asset_List_Category_ID AND rockwell.logix_asset_list.Vendor_ID = rockwell.vendor.Vendor_ID AND&#13;&#10;rockwell.logix_asset_list.Part_Number = ?"                                               UpdateCommand="UPDATE vendor, logix_asset_list                                                set                                                vendor.vendor_name= @Vendor_Name,                                                logix_asset_list.Total_Quantity=@Total_Quantity,                                               logix_asset_list.Quantity_In_Stock=@Quantity_In_Stock,                                               logix_asset_list.Part_Number=@Part_Number,                                               logix_asset_list.On_Order=@On_Order,                                               logix_asset_list.Description=@Description                                               WHERE                                               logix_asset_list.Vendor_ID = vendor.Vendor_ID                                               and logix_asset_list.Inventory_ID = @Inventory_ID" >                               <SelectParameters>                                   <asp:ControlParameter ControlID="rstGrid" Name="?" PropertyName="SelectedValue" />                               </SelectParameters>                               <UpdateParameters>                               <asp:ControlParameter Type="Int32" ControlID="Total_Quantity" ConvertEmptyStringToNull="true" Name="@Total_Quantity"/>                               <asp:ControlParameter Type="string" ControlID="Vendor_Name" ConvertEmptyStringToNull="true" Name="@Vendor_Name"/>                               <asp:ControlParameter Type="Int32" ControlID="Quantity_In_Stock" ConvertEmptyStringToNull="true" Name="@Quantity_In_Stock"/>                               <asp:ControlParameter Type="string" ControlID="Part_Number" ConvertEmptyStringToNull="true" Name="@Part_Number"/>                               <asp:ControlParameter Type="string" ControlID="On_Order" ConvertEmptyStringToNull="true" Name="@On_Order"/>                               <asp:ControlParameter Type="string" ControlID="Description" ConvertEmptyStringToNull="true" Name="@Description"/>                               <asp:ControlParameter Type="Int32" ControlID="Inventory_ID" ConvertEmptyStringToNull="true" Name="@Inventory_ID"/>                               </UpdateParameters>                            </asp:SqlDataSource> <asp:FormView ID="FormView1" runat="server" BackColor="White" BorderColor="#DEDFDE"                                BorderStyle="None" BorderWidth="1px" CellPadding="4" DataSourceID="invDetSrc"                                ForeColor="Black" GridLines="Vertical" DataKeyNames="Inventory_ID"> <EditItemTemplate>                                    <table border="0" cellpadding="3" cellspacing="3" style="background-color: white; color: black;">                                        <tr>                                            <td colspan="2">                                                <asp:Image ID="Image1" runat="server" Height="150px" ImageUrl='<%# "~/Image/photo/Logix%20Asset/"+Eval("Category_Name")+"/"+Eval("ImageUrl") %>'                                                    Width="150px" />                                                <br />                                                <asp:HyperLink ID="drawingLink" runat="server" NavigateUrl='<%# "~/Image/drawing/Logix%20Asset/"+Eval("Category_Name")+"/"+Eval("DrawingUrl") %>'                                                    Target="_blank" Text="View Drawing" ForeColor="Blue"></asp:HyperLink>                                            </td>                                            <asp:HiddenField ID="HiddenField1" runat="server" Value='<%# Bind("Inventory_ID") %>' />                                        </tr>                                        <tr>                                            <td style="color: black">                                                Part Number:</td>                                            <td>                                                <asp:TextBox ID="Part_Number" runat="server" Text='<%# Bind("Part_Number")%>'></asp:TextBox>                                                </td>                                        </tr>                                        <tr>                                            <td>                                                <strong style="color: black">Vendor:</strong></td>                                            <td>                                                <asp:TextBox ID="Vendor_Name" runat="server" Text='<%# Bind("Vendor_Name")%>'></asp:TextBox>                                                </td>                                        </tr>                                        <tr>                                            <td style="color: black">                                                <strong>Total Quantity:</strong></td>                                            <td>                                                <asp:TextBox ID="Total_Quantity" runat="server" Text='<%# Bind("Total_Quantity")%>'></asp:TextBox>                                                </td>                                        </tr>                                        <tr>                                            <td style="color: black">                                                <strong>Quantity In Stock:</strong></td>                                            <td>                                                <asp:TextBox ID="Quantity_In_Stock" runat="server" Text='<%# Bind("Quantity_In_Stock")%>'></asp:TextBox>                                         </td>                                        </tr>                                        <tr>                                            <td style="color: black">                                                <strong>Quantity on Order:</strong></td>                                            <td>                                                <asp:TextBox ID="On_Order" runat="server" Text='<%# Bind("On_Order")%>'></asp:TextBox>                                            </td>                                        </tr>                                        <tr>                                            <td style="color: black; height: 44px;">                                                <strong>Description:</strong></td>                                            <td style="height: 44px">                                            <asp:TextBox ID="Description" runat="server" Text='<%# Bind("Description")%>' Height="53px" TextMode="MultiLine" Width="150px"></asp:TextBox></td>                                        </tr>                                        <tr>                                            <td>                                                <asp:Button ID="EditBtn" runat="server"                                                    Text="Submit Changes" Width="106px" CommandName="Update" /></td>                                            <td>                                                &nbsp;<asp:Button ID="Cancel" runat="server" Text="Cancel" CommandName="cancel" /></td>                                        </tr>                                    </table>                                </EditItemTemplate>                            </asp:FormView>any help would be much appreciated!   

View 1 Replies View Related

Update Multiple Columns In One Sql Statements

Nov 2, 2004

I am almost sure I can update variables columns in one select/case type
statement, but having problems working out the syntax.

I have a table with transactions - with tran types as the key.

in this example, types = A,B,C ,D.

in this first example I am updating the sum of QTY to value t_A based on
tran types =A.

can I perform sub query/case to update with the same where clause
but for types B,C and D?? I also have to insert for specific lot numbers each sum values.



Create table #t_reconcile(
t_lot_number int not null,
t_A float,
t_B float,
t_C float,
t_D float)


insert #t_reconcile

select t.lot_number, sum(t.qty)
from i , t
where i._id = t.event_id
i.transaction_type = 'A'
group by t.lot_number
order by t.lot_number

View 3 Replies View Related

Condensing Multiple Update Statements

Mar 18, 2008

I'm working on a query which involves changing the case of a field from mixed case to all lower case. The field exists in multiple tables, so to do this I have multiple update statements. Is there a way to make this more efficient?

See below for example:

update InvestBroker
set BrokerID = lower(BrokerID)

update InvestFill
set BrokerID = lower(BrokerID)

update InvestBrokerAccount
set BrokerID = lower(BrokerID)

update InvestFIXBroker
set BrokerID = lower(BrokerID)

update InvestUploadBrokerFilter
set BrokerID = lower(BrokerID)

update InvestSettleInstructions
set BrokerID = lower(BrokerID)

View 2 Replies View Related

Update Column In Multiple Tables

Jun 22, 2004

I have multiple product tables with a "ListPrice" column. I want to use a temporary table that contains "ProductID" and "ListPrice" columns and execute a stored procedure that will update the "ListPrice" column in all product tables with the "ListPrice" from temporary table where "ProductID" from temporary table matches "ProductID" in product tables.

I am new at writing stored procedures.

View 2 Replies View Related

Possible To UPDATE Multiple Fields Conditionally?

Nov 14, 2013

I have a am doing some date calcs () . The situation is that I have a Move date (date a customer moved to a new home). I want to calculate their sales for the following 0-3 months after the move (month 0 being the move month). I have the month and year of the move (MthStart, YrStart), and I am adding 3 to MthStart to get the MthEnd of that 0-3 month period. I will then find sales BETWEEN YrStart&MthStart AND YrEnd&MthEnd (there is a YrMth field in the sales table)

Of course, for MthStarts 10, 11, and 12, the ends are 13, 14, and 15. So for these, I need to subtract 12, and increment the YrEnd by 1.

I am wondering if there is a way to update both the MthEnd and YrEnd fields at one time instead of separate SETs (or maybe I am just thinking about this the hard way to begin with). Is there a way to update both in a single CASE statement like WHEN MthEnd> 12 THEN MthEnd-12 AND YrEnd+1?

Code:
CREATE TABLE #myTable (
YrStart INT,
MthStart INT,
YrEnd INT,
MthEnd INT);

[code]....

View 4 Replies View Related

T-SQL (SS2K8) :: Update Multiple Columns In A CTE?

Sep 29, 2015

Can you update data from multiple tables in the same UPDATE statement, by joining those tables in a CTE ?

For example, this fails:

DECLARE @UPDCATE_COUNT AS int = 100000;
WITH COMBINED_TABLES AS (
SELECT TOP (@UPDATE_COUNT) T.UpdateID, T.IS_UPDATED, U.[Description]
FROM dbo.Table1 AS U
INNER JOIN dbo.Table2 AS T

[code]....

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

Error On Trigger When Doing Multiple Row Update?

Apr 15, 2014

I have the following trigger that updates a couple test fields to null when they are 1/1/1900, works great on inserts, and one line updates:

CREATE TRIGGER UpdateDate
ON test
AFTER INSERT, UPDATE
AS
UPDATE Test
SET
[CheckDate] = CASE WHEN [CheckDate] = '19000101' THEN NULL ELSE [CheckDate] END,
[CheckDate2] = CASE WHEN [CheckDate2] = '19000101' THEN NULL ELSE [CheckDate2] END
where AutoID = (select AutoID from inserted)

However, when trying to do a multi line update statement, I get the following error:

Msg 512, Level 16, State 1, Procedure UpdateDate, Line 7

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

How can I get around this?

View 5 Replies View Related

Update Multiple Rows In One Table?

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

Function To UPDATE Multiple Records

Jan 7, 2015

I have a question, what kind of Query or function do I have to use to UPDATE multiple record that I just want to add a letter at the end of the existing OrderNum.

Before
ID NAME OrderNum
1 Pete WEB123
2 Paul WEB124
3 Sam WEB125
4 Tim WEB126

After
ID NAME OrderNum
1 Pete WEB123A
2 Paul WEB124A
3 Sam WEB125A
4 Tim WEB126A

View 3 Replies View Related

UPDATE Statement With Multiple Criteria

Apr 5, 2006

I am trying write a query to update a column of data in my xLegHdr table however the update is based on multiple criteria. I was trying to use "IF..ELSE" statements but that is not working.

I would like to update the "SMiles" column based on the data in the "Dist" column. If the number in the "Dist" column is less than 250 then subtract 25 and multiply it by 1.15 the result should go in the "SMiles" column. If the number is grater than 250 then subtract 40 and multiply by 1.15 and place the result in the "SMiles" column; like so:

UPDATE xLegHdr
SET SMiles =
IF Dist<250 THEN Round(Dist-25)*1.15)
ELSE Round(Dist-40)*1.15)
END IF

Any ideas?

View 1 Replies View Related







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