Update alias or update tablename when from clause is specified

• ScottPletcher

  SSC Guru

  Points: 101166

  More actions

  December 16, 2021 at 9:39 pm

  #3964567

  Jeff Moden wrote:

  You have to understand... I don't actually care what the question was.  This isn't stack overflow.  I was teaching about the proper forms and you don't need a FROM clause for a non-joined update.

  I prefer it that way because it's then much easier if you need to add a join later, when you will want to assign an alias.

  That is, if you write:

  update tbl
  set col = 'value'
  from dbo.tablename tbl
  where tbl.keycol = 1

  and you later need to join it,  you simply add the join and, if needed, another set value:

  update tbl
  set col = 'value', col2 = ot.data_col
  from dbo.tablename tbl
  inner join dbo.othertable ot ON ot.key_col = tbl.key_col
  where tbl.keycol = 1

  But if you wrote this (which, sure, is valid code):

  update dbo.tablename
  set col = 'value'
  where keycol = 1

  then you have to fully convert it to look like the code above, because you simply can't do a JOIN without a FROM first.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Jeff Moden

  SSC Guru

  Points: 1004530

  More actions

  December 16, 2021 at 10:34 pm

  #3964586

  There is no question in my mind that the form of updating an alias and having a FROM clause is easier in a lot of respects.  I even said that previously about how easy it is to change it to a SELECT for troubleshooting purposes.  That has nothing to do with what I was talking about in for the first form.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004530

  More actions

  December 17, 2021 at 12:36 am

  #3964609

  ScottPletcher wrote:

  Once you assign an alias to a table, that alias is the only valid way to reference that table.  That's not a matter of preference or opinion.  It's a SQL fact.

  SELECT TOP (5) * FROM sys.objects --OK

  SELECT TOP (5) sys.objects.* FROM sys.objects --OK

  SELECT TOP (5) o.* FROM sys.objects o --OK

  SELECT TOP (5) sys.objects.* FROM sys.objects o --ERROR! You've assigned an alias, so the original name IS NO LONGER VALID!

  BWAAA-HAAA-HAAA!!!  I'll meet your fact and raise you an exception...

   CREATE TABLE dbo.UpdateTest
          (
           RowNum INT IDENTITY(1,1)
          ,ColA CHAR(10)
          ,ColB CHAR(10)
          )
  ;
   INSERT INTO dbo.UpdateTest
          (ColA,ColB)
   VALUES  ('A1','B1')
          ,('A2','B2')
  ;
   UPDATE dbo.UpdateTest
      SET dbo.UpdateTest.ColA = 'XX'
     FROM dbo.UpdateTest ut
  ;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ScottPletcher

  SSC Guru

  Points: 101166

  More actions

  December 17, 2021 at 1:35 am

  #3964619

  Jeff Moden wrote:

  ScottPletcher wrote:

  Once you assign an alias to a table, that alias is the only valid way to reference that table.  That's not a matter of preference or opinion.  It's a SQL fact.

  SELECT TOP (5) * FROM sys.objects --OK SELECT TOP (5) sys.objects.* FROM sys.objects --OK SELECT TOP (5) o.* FROM sys.objects o --OK SELECT TOP (5) sys.objects.* FROM sys.objects o --ERROR! You've assigned an alias, so the original name IS NO LONGER VALID!

  BWAAA-HAAA-HAAA!!!  I'll meet your fact and raise you an exception...

   CREATE TABLE dbo.UpdateTest
          (
           RowNum INT IDENTITY(1,1)
          ,ColA CHAR(10)
          ,ColB CHAR(10)
          )
  ;
   INSERT INTO dbo.UpdateTest
          (ColA,ColB)
   VALUES  ('A1','B1')
          ,('A2','B2')
  ;
   UPDATE dbo.UpdateTest
      SET dbo.UpdateTest.ColA = 'XX'
     FROM dbo.UpdateTest ut
  ;

  We've already seen that SQL allows that syntax, but it's still a horrible mistake.  I'm not sure why you'd want to propagate a worst practice.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".