1

I would like to be able to update a table at once, instead of multiple statements and I don't want to make a temporary table.

To test this, I made this table:

USE [SomeSchema]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SomeSchema].[TestTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TextField] [varchar](250) NULL,
    [updateField] [varchar](20) NULL,
 CONSTRAINT [Pk_TestTable_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [Idx_TestTable] UNIQUE NONCLUSTERED 
(
    [TextField] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I tried to combine these two answers (https://stackoverflow.com/a/57965771 and https://stackoverflow.com/a/32431922/369122) into a working statement.

My last try:

WITH NewData AS 
(
    SELECT * FROM ( VALUES  ('abc', 'A'),('def','d'),('ghi','g')) 
    x 
    (TextField, updateField)
)
update [SomeSchema].[TestTable]
set _a.updateField= _b.updateField
from 
[SomeSchema].[TestTable] _a,
NewData _b 
where _a.TextField=_b.TextField

Gave this error: Msg 4104, Level 16, State 1, Line 22 The multi-part identifier "_a.updateField" could not be bound.

Any suggestions? For the record; this is just a test. In practice I need to be able to join multiple columns to update one or more columns. thanks,

Matthijs

@larnu's answer did the job:

"As for the problem, replace update [SomeSchema].[TestTable] with update _a. You're referencing a table in your FROM as the column to UPDATE, but the table your updating is defined as a different instance of [TestTable]"

WITH NewData AS 
(
    SELECT * FROM ( VALUES  ('abc', 'a'),('def','d'),('ghi','g')) 
    x 
    (TextField, updateField)
)
update _tt
set _tt.updateField= _nd.updateField
from 
[SomeSchema].[TestTable] _tt
left join
NewData _nd 
on _tt.TextField=_nd.TextField
user369122
  • 792
  • 3
  • 13
  • 33
  • 5
    The "new" `JOIN` syntax has been around for 27 years now; it's long past the point of not using it: [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) – Thom A Oct 31 '19 at 10:20
  • Refer https://blog.sqlauthority.com/2013/04/30/sql-server-update-from-select-statement-using-join-in-update-statement-multiple-tables-in-update-statement/ – Gehan Fernando Oct 31 '19 at 10:20
  • 1
    As for the problem, replace `update [SomeSchema].[TestTable]` with `update _a`. You're referencing a table in your `FROM` as the column to `UPDATE`, but the table your updating is defined as a different instance of `[TestTable]`. Also use good aliases `TestTable` starts with a `T`, so `TT` would be a far better alias, and `ND` for `NewData` ("NewData" doesn't even have a "b" in it). [Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3) – Thom A Oct 31 '19 at 10:21
  • Also, `;` isn't a "beginninagtor" it does at the **end** of your statements, not the start. It's a Statement Terminator. – Thom A Oct 31 '19 at 10:21
  • Thanks @Larnu ! You second comment did the job (and normally I do indeed use correct aliases ;-)) – user369122 Oct 31 '19 at 10:36
  • @Larnu . . . Sadly, SQL Server allows the referenced update table to *not* use the alias. – Gordon Linoff Oct 31 '19 at 10:39
  • It does, @GordonLinoff, but the OP has declared the table to update as `[SomeSchema].[TestTable]` **without** the alias, but then references the table `[SomeSchema].[TestTable]` **with** an alias (in their `FROM`) as the destination to update. That would be like doing `UPDATE TableA SET TableB.Column=TableC.Column FROM TableB ...` – Thom A Oct 31 '19 at 10:49
  • @Larnu . . . I know. SQL Server interprets the table name as the reference in the `FROM`. Here is a db<>fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ac86827af7ac4ca14e66b8e1c102a5bf. This is HORRIBLE behavior. It does occur to me that this might not work with commas in the `FROM` clause, but I don't even want to bother testing. – Gordon Linoff Oct 31 '19 at 12:35
  • That isn't really the same though, @GordonLinoff. What the OP has is more equivalent to this [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=63d5ab27896938bb4fe7a8b6cd45154c). Though, I agree, the only syntax you should be using is this [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=e9bd4bb099833c201bf907af454028f9), to avoid ambiguity. – Thom A Oct 31 '19 at 12:52

0 Answers0