InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 22:00:01 GMT20http://www.sqlservercentral.com/Forums/Topic1255858-90-1.aspxRe this format which I've never come across before:"UPDATE Product SET Price = N.PriceFROM Product P JOIN New_Prices N ON P.ID = N.IDWHERE P.ProductName = 'Doo-Dads';"Does anyone know the Oracle equivalent of this? So far, I've always used this format"UPDATE Product P SET P.Price = (SELECT N.Price FROM New_Prices N WHERE P.ID = N.ID);"(script not checked for correctness, it's just an example of the structure)in both Oracle and SQLServer which is OK for one column but a PITA for more than one column as you have to repeat the SELECT for every column.Wed, 31 Oct 2012 03:47:12 GMTmarlon.setonhttp://www.sqlservercentral.com/Forums/Topic1255858-90-1.aspxI found the output clause very timely for my project: thanks! You might want to look at figure 7, though. It is a repeat of figure 6.Mon, 15 Oct 2012 08:08:20 GMTJudy Zingherhttp://www.sqlservercentral.com/Forums/Topic1255858-90-1.aspx[quote][b]Mr.DiGi (10/10/2012)[/b][hr]I like "less magic" - you can update table alias:[code="sql"]UPDATE PSET ProductName = N.ProductName, Price = N.PriceFROM dbo.Product PINNER JOIN dbo.New_Prices N ON P.ID = N.ID;[/code]This is great when you join "Product" table more than once.[/quote]You need to be careful using this syntax when the ID column isn't unique in New_Prices. You may end up with the Product table being updated based on the "wrong" (ie unexpected) row from New_Prices. You won't get an error message - it'll just do it.You can use MERGE instead (SQL Server 2008 onwards), or the ANSI-compliant UPDATE syntax. Please see [url=http://www.sqlservercentral.com/Forums/Topic1054628-392-2.aspx]here[/url] for some of the pros and cons.Please note my intention is not to start another quasi-religious debate on the subject, just to bring these issues to readers' attention.JohnEdit: thanks for the article by the way, Greg. I learned something new about the .WRITE syntax.Thu, 11 Oct 2012 03:50:13 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1255858-90-1.aspxEarly in the article you mention the benefit of creating a SELECT statement to preview the rows that will be updated. Here is a useful form for previewing the changes:[code="sql"]UPDATE p SET--SELECT p.*, ProductName = 'WingDing', Price = 19.27FROM Product pWHERE p.ProductName = 'WingDng'[/code]Wed, 10 Oct 2012 07:53:20 GMTgorthoghttp://www.sqlservercentral.com/Forums/Topic1255858-90-1.aspx[quote][b]simon.duckett (10/10/2012)[/b][hr]Good article.Listing 7 is an incorrect script. It is a repeat of Script 6.[/quote]Correct, I second that. The first script example under the .WRITE section is a duplicate of the preceding script example.Wed, 10 Oct 2012 05:21:41 GMTINCREDIBLEmousehttp://www.sqlservercentral.com/Forums/Topic1255858-90-1.aspxHow about joined aggregate values?Also, you missed out the rather handy ability to use variables in the set.egset @Variable = column = @variable + stuffetcWed, 10 Oct 2012 04:30:01 GMTRichBhttp://www.sqlservercentral.com/Forums/Topic1255858-90-1.aspxGood article.Listing 7 is an incorrect script. It is a repeat of Script 6.Wed, 10 Oct 2012 00:36:52 GMTsimon.ducketthttp://www.sqlservercentral.com/Forums/Topic1255858-90-1.aspxI like "less magic" - you can update table alias:[code="sql"]UPDATE PSET ProductName = N.ProductName, Price = N.PriceFROM dbo.Product PINNER JOIN dbo.New_Prices N ON P.ID = N.ID;[/code]This is great when you join "Product" table more than once.Wed, 10 Oct 2012 00:34:50 GMTMr.DiGihttp://www.sqlservercentral.com/Forums/Topic1255858-90-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Stairway+Series/88680/">stairway to T-SQL DML Level 10: Changing Data with the UPDATE Statement</A>[/B]Wed, 22 Feb 2012 05:25:51 GMTGreg Larsen