Update whit values from other table

I have one table named estoque with this fields
Produto  int
ICM         int


and the second table produtov1 with this fields
Produto  int
ICM         int

I want to update all the column ICM of the second table produtov1 where the 
field
produto is = , I have write this but not work

UPDATE produtov1
SET icm = estoque.icm
WHERE produtov1.produto = estoque.produto

Thanks by any help or ideia



 


0
Alejandro
8/13/2010 7:58:43 AM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
747 Views

Similar Articles

[PageSpeed] 5

You need to use a subquery in the SET clause. Alternatively you could
use UPDATE FROM, but be carefull as you get random results if more
than one row in the source table is matching.

Some reading here:
http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx

brgds

Philipp Post

0
Philipp
8/13/2010 9:12:57 AM
"Alejandro Carnero" <alecarnero@uol.com.br> wrote in message 
news:Oc$MW1rOLHA.2276@TK2MSFTNGP06.phx.gbl...
>I have one table named estoque with this fields
> Produto  int
> ICM         int
>
>
> and the second table produtov1 with this fields
> Produto  int
> ICM         int
>
> I want to update all the column ICM of the second table produtov1 where 
> the field
> produto is = , I have write this but not work
>
> UPDATE produtov1
> SET icm = estoque.icm
> WHERE produtov1.produto = estoque.produto
>

 UPDATE produtov1
SET icm = estoque.icm
FROM estoque
WHERE produtov1.produto = estoque.produto


0
Scott
8/13/2010 11:57:03 AM
Below are example of the ANSI-standard subquery method along with the SQL 
Server proprietary UPDATE...FROM syntax.  In addition to the multi-row 
consideration Phillip mentioned, be aware that you will get different 
results for non-matching rows.  The subquery method will set the target 
column to NULL when no rows match (unless further limited via a WHERE 
clause) whereas the UPDATE...FROM will update only those rows that match. 
Consider the following:

CREATE TABLE dbo.produtov1(
	produto int NOT NULL CONSTRAINT PK_produtov1 PRIMARY KEY,
	ICM int NULL
);

CREATE TABLE dbo.estoque(
	produto int NOT NULL CONSTRAINT PK_estoque PRIMARY KEY,
	ICM int NULL
);

INSERT INTO dbo.produtov1
VALUES
	(1,0)
	,(3,0)
	,(5,0)
	,(7,0);

INSERT INTO dbo.estoque
VALUES
	(1,1)
	,(2,2)
	,(3,3)
	,(4,4)	,(7,7);
GO

UPDATE dbo.produtov1
SET icm = (
    SELECT estoque.icm FROM dbo.estoque
    WHERE produtov1.produto = estoque.produto);

Results:

produto	ICM
1	1
3	3
5	NULL
7	7

Repeating the test with the query below:

UPDATE produtov1
SET icm = estoque.icm
FROM estoque
WHERE produtov1.produto = estoque.produto;

Results:

produto	ICM
1	1
3	3
5	0
7	7

-- 
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Alejandro Carnero" <alecarnero@uol.com.br> wrote in message 
news:Oc$MW1rOLHA.2276@TK2MSFTNGP06.phx.gbl...
> I have one table named estoque with this fields
> Produto  int
> ICM         int
>
>
> and the second table produtov1 with this fields
> Produto  int
> ICM         int
>
> I want to update all the column ICM of the second table produtov1 where 
> the field
> produto is = , I have write this but not work
>
> UPDATE produtov1
> SET icm = estoque.icm
> WHERE produtov1.produto = estoque.produto
>
> Thanks by any help or ideia
>
>
>
>
>
> 
0
Dan
8/13/2010 12:21:49 PM
Reply:

Similar Artilces:

how to use temporary tables in dex
Hi Somebody has one example of how to use temporary tables in dex Cesar Hi Cesar. Define temp tables just as you would define a SQL table in Dex - but with physical name as "temp" without the quotation. I recommend using database type ctree for performance as it would just create a temp file in your directory. From there on, just use the temp table as you would with normal dex table but remember that once you are done with your routine, your temp table will be deleted automatically. Use temp table as a temporary repository for your calculations, reports, etc. ---Darryl Baj...

Tab control question about updates
Searched but couldn't see this discussed. I have a form with 5 tabs. Data can be loaded three ways, manually entered, imported from a text file, and imported from a Word template. On one of the tabs (the second) when the user enters information such as a social sec. number (SSN) that matches a file already in the system, a flag goes off and alerts the user. Works fine. On the import functions, the data is loaded and appears correctly, but when the user moves to the second tab where the SSN number appears, it does not set off the flag when the page opens. I can make it happen with a c...

How do I add a secondary axis to a pivot table chart?
Using XL2007 - the secondary axis radio buttons are greyed out. Is there a way to add the 2nd axis? Hi, This is chart type dependent. Suppose you have a line chart, right click the series and choose Format Series, the Axis radio button should be available. What type of chart are you using? Cheers, Shane Devenshire "dangelor" <dangelor@discussions.microsoft.com> wrote in message news:2610028F-5FAF-4400-A3E3-B7D0CCEFB443@microsoft.com... > Using XL2007 - the secondary axis radio buttons are greyed out. Is there a > way to add the 2nd axis? ...

Pivot Table #5
I create a pivot table of growing data each month. Now when I create the pivot table after I set up the layout. I see the message "calculating Pivot Table" but nothing happens. Is there some setting I need to change on my computer. All I get now is a empty new worksheet. ...

Table requires Custom Linking to Excel
I have a table in Word 2000 with various columns. Each row represents one order for a meal package. One of the columns indicates one of three baked pies to order (apple, cherry or pumpkin). Another column indicates whether or not the order has been paid or unpaid. Is there a way to put a total number for each of the pie types ordered into an Excel worksheet? Similarly, would there be a way to analyze all of the rows, and for all rows with an unpaid status, multiply this number by the cost for each meal package, and display this result in an Excel worksheet as money due? Thanks...

UPDATE using SUM
I have a stored procedure where I am trying to update a temp table in the flow of the sp. I have tried the code below but it gives me an error "Incorrect syntax near the keyword 'GROUP' and I understand the error but don't know how to get around it and still get sums. Can anyone help? Thanks. UPDATE #tempInventoryAnalysis SET [PTDSalesQty] = SUM(CASE WHEN MGB.dbo.tblArHistHeader.InvcDate BETWEEN @StartDate AND @EndDate THEN MGB.dbo.tblArHistDetail.QtyShipSell ELSE 0 END), [YTDSalesQty] = SUM(MGB.dbo.tblArHistDetail.Qt...

Setting random number range based on query, not table
I have a "quiz" form that randomly pulls up questions from my table. It works fine, and I don't mind that it repeats questions. This allows me to sit and review for as long as I want. The problem is, that I want to use criteria in a query to limit the available questions. If I base this form on a query and limit the number of questions, the code I am using is still setting the maximum number as the number of records in my table. In the past, I had a similar database (which I have lost) that would open the form, set the selector to the last record available to the form,...

Update Download problem
I downloaded the latest update for Outlook 2000. I did NOT have the installation cd handy. Okay, usually no problem, the instal quits. However this time, it did quit, yet now I am unable to open excel. It launches the instal program. The download was for Outlook. It has me boggled. Outlook, Word and Access open with no known problems. I have tried to open excel different times/ways. It continues to try to load the update. Any reasoning on the subject??? Thanks* ...

pivot tables #9
i posted this on the general forum but i thought maybe someone here can help me. I have a table that has four regions and total sales amount for each reason by week. my pivot table lists all those and totals it per region per month. i want to add in the pivot table a percentage of the regions sales for that one week over the total sales for all four regions. is there a way i can add that to the table? i.e. the table is currently like this: Week 1 East 500 Midwest 487 South 529 West 492 Total 2008 i want to add the percentages like this: Week 1 East ...

display the value of an unbound textbox in a field within a table
I created 3 textboxs to calculate the number of business days between 2 dates. The 3rd textbox contains the value. I now want to have this value displayed in a table field. I'm sure this is easy but I'm a complete novice and have spent far too much time on this already. Can anyone help! Fibi, The general concept of tables is that they are for storage of data in the background. They are not for display of data. That is what forms and reports are for. Therefore, the appearance of the data in the tables is not normally relevant, and the display of calculated values is im...

get/change first operation on table 'uprEmployeeCount' failed
I created a 'test' company on V10, restored the backup from our real company into this test company. I have inactivated all the employees in the test company. Unless I enter Dynamics as sa, I get the following message: A get/change first operation on table 'uprEmployeeCount' failed accessing SQL Data. Under the more info button: [Microsoft][ODBC SQL Server Drive][SQL Server] The EXECUTE permission was denied on the object 'zDP_UPR41600F_1',database 'DYNAMICS', schemo 'dbo'. I have deleted the UPR41600 table and recreated it but I still get the...

Pivot Table Refresh error "Problems Obtaining Data"
I can't seem to get data or change the data source. When I refresh, I get the error message "Problems Obtaining Data" When I start the Pivot Table Wizard, the back button is grayed out so that I can't go back and change data sources. Any help is appreciated. ...

How the heck do I find tables, views, forms etc... in Access 2007
Can Access 2007 navigation be any more convoluted? I've been spending close to an hour trying to find a single table, query form, etc... now that the navigation has been changed. Is there a way to get the old style back? It does take some time - more than an hour :-) - to become familiar with the new interface in A2007 (NavPane, ribbon, ...) Suggestions: - The title bar at the top of the Nav Pane includes a tick box for: All Access Objects - Set Category to: Object Type - The categories (Tables, Queries, ...) collapse. - Show and use the Search Bar. It filters objects as...

multiple fies updating one file
Hi, How would I setup an excel sheet that needs to collect numbers from other excel sheets. The rows and fields numbers will keep growing. I know how to reference a cell in a file but not sure how you can quickly create a reference to a range of cells and how about when the range keeps growing. Thank you Hi Vic; You can select and copy the range you want to link, then go to then go to the receiving sheet and click the top right cell. Click Edit / Paste Special and select Paste Link. This will link the entire range. If you happen to be running Office 2007 you can right click into the ...

Pivot table returns `
I have data from an SQL WBC-A. When I pivot this data it returns ` (The character below the tilde) Any other data returns the correct pivot. E.g. WBC-X returns WBC-X ...

How do I set up formulas to update graphs dynamically
Hello, I have the following information I need to graph date Feb-08 Mar-08 Apr-08 May-08 Jun-08 Jul-08 burn 116 23 254 inventory 3035 3011 2757 The "date" is the x-axis label and the "burn" and "inventory" are the values on the y-axis. If the values for burn and inventory are blank for a particular date, I don't want the information plotted. As I add values for each date, I would like the graph to be updated automatically. I understand I need to define a named range and use it in the graph, however, I have not been successful in doing this. Also, even t...

TargetInvocationException in Update Invoice in Microsoft Dynamics
Hi, we get the follow error mssage when we try update the invoice object. But, in the same class, we update the salesorder object with sucess. Obs. Our code is a custom page. Any ideas what this could be caused by? Tks Clau >Crm Exception: Message: , ErrorCode: -2147197184, InnerException: System.Runtime.InteropServices.COMException (0x80045F00): <details></details> at Microsoft.Crm.Platform.ComProxy.CRMInvoiceClass.Update(CUserAuth& Caller, String InvoiceId, String InvoiceXml) [2006-10-10 12:16:47.4] Process: w3wp |Thread: 8708 |Category: Platform |User: d12c7...

Read XML into Dataset and load SQL server table from Dataset
Hello all, I am working on a project with the following characteristics: 1. Load data from a SQL server table to an xml file 2. Read the xml file into a dataset. 3. Load data from the dataset into another SQL server table. I was able to accomplish the first objective. I used the following code to read the generated xml file into a dataset. Dim strXml As String = "C:\Customers.xml" Dim sr As StreamReader = New StreamReader(strXml) Dim ds As DataSet = New DataSet ds.ReadXml(sr, XmlReadMode.IgnoreSchema) How can I traverse in the dataset and post each row to my desired table on ...

Tables and banding color in background
I am creating a template where many tables will need to be used. Our standard is to have banding of rows in the table, and when users want to expand the table, I would like them to be able to have the banding automatically occur. This option appears in Word 2007, but does not function well. Has anyone had success with this functionality? Thanks in advance for your help. Color banding can be applied as part of a table style. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "sharon27lily" <sharon27lily@di...

DailySales Table
Hi There, Does anyone know what Type & TypeID columns referring to in DailySales table in HQ ? I have customization guide but there is no details about that columns. Thanks, Arthur Hi Arthur - my understanding of the Daily Sales table is that it gets populated by some internal function that groups based on supplier, cashier, etc. Here are some old notes I found on what the Daily Sales Type refer to: 1: Supplier 2: Category 3: Department 4: Register 5: Cashier 6: Sales Rep -- the TypeID may refer to the batch that's feeding the info (ie batch.batchnumber ...

pasting or moving formula cells without updating formulas
I have a flat spreadsheet with a results page at the end. The results page contains a set of formulae which refer to various cell locations within the body of the spreadsheet in order to return statistical results based on the values in said cells. Now I'd like to add more data to my spreadsheet, so i need to make it bigger; however, when I copy and paste, or select and drag the cells containing the formulae, Excel updates the formulae so that they refer to different cells which bear the same spatial relationship to the formulae as the original referees did before the formulae were ...

How can I plot every 65th value out of 6500 values in Excel?
I have data entered in 6500 cells, in size order, but only want to plot every 65th cell so as to build a representative distribution. How can I instruct Excel to skip cells for graphing? Thanks in advance, Bill Hi, Here are a couple of approaches to the problem. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon024 http://www.tushar-mehta.com/excel/newsgroups/only_some_markers/index.html Cheers Andy Bill Viverette wrote: > I have data entered in 6500 cells, in size order, but only want to plot every > 65th cell so as to build a representative distribution. >...

how to program recurring value
I wish every entry of a colums to be a the same function of the entry above. How do I do that? Leo Hi Leo! Assuming that you have your first formula entered Select the range covered by the common entry formula F2 Ctrl + Enter -- -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Leo Kerner" <l.kerner@sympatico.ca> wrote in message news:404294EF....

Add value when recording a macro
Hello, I recorded a macro to create a bar chart - I checked the checkbox to add the values. Stopped recording and showed me the chart with the values. If I run the macro again, it does not display the value. What is the code I need to always display the value? -- LizW After you create the chart, turn on the macro recorder again Select the chart, and choose Chart>Chart Options On the Data Labels tab, check Values Click OK Turn off the macro recorder In the recorded code, you'll see a line similar to this: ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _ Ha...

Counting Null Values in a Report
I have a report that is grouped by people, then by Reason Closed. I want to count how many entries do not have a closed date. I tried the previous posts but could not get it to work. I have a group header for each person to group their categories together. Thanks. Hi Dea, Try: =Sum(-IsNull([ClosedDateFieldName])) Note the minus sign just after the first paranthesis. IsNull() returns a -1 when the item is null, otherwise 0. So by summing up the negative of each (-1)s you are in essence counting 1 for each null item. Alternatively you could move the minus...