Re: Copy entire column from one table to another (sql)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


"Dmitriy Antonov" <antonovdima@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23Ejs2PE0GHA.4932@xxxxxxxxxxxxxxxxxxxxxxx

"Mark J. McGinty" <mmcginty@xxxxxxxxxxxxxxx> wrote in message
news:OBFPn4yzGHA.3908@xxxxxxxxxxxxxxxxxxxxxxx

[snip]

Run the following script in Query Analizer (you can use Pubs or Northwind
or any other testing DB). It should run without any problem using proposed
syntax (tested in MS SQL2000).

Hmm... you're right, my bad... Found this in BOL, maybe I reached that
conclusion when working with a table that was joined to itself...

If the table being updated is the same as the table in the FROM clause,
and there is only one reference to the table in the FROM clause,
table_alias may or may not be specified. If the table being updated
appears more than one time in the FROM clause, one (and only one)
reference to the table must not specify a table alias. All other
references to the table in the FROM clause must include a table alias. <<

But that (copy/pasted straight from SQL2K BOL) isn't true either, I just
tried multiple self joins, all aliased and it worked that way too. Thought
maybe ADO changed the rules a bit, but it works there too... If I still had
SQL 7 running anywhere I'd check to see if maybe that's where I formed that
assumption, but regardless... clearly it no longer applies.


And I never heard about the problem with particular order of tables, when
joining them - have you actually had them (problems) and can give
examples, when having the different order would fail in similar case?

Yes I've seen it happen, sadly I don't have any canned examples... I
recently saw some quirky behavior when a view that returned the table being
updated statically filtered on one field, was used in the FROM clause, and
there were no other instances of the same table, i.e.,

UPDATE Table1 SET [...] FROM Table1Active [...join ... where ...]

Given: CREATE VIEW Table1Active AS SELECT * FROM Table1 WHERE Active = 1

The where clause for the update statement (which resides in a SP) included
[identity] = @id, and thus logically should've limited it to at most 1 row,
but when executed, one of the columns got updated in every row in the table.
All columns in the intended row were updated as expected.

I changed it to conform with my "update-dest table equals first table in
from clause" rule, and it worked as expected.

The first time I encountered strange things with UPDATE ... FROM I did some
research and experimentation. From the tech ref it wasn't clear to me at
all what the positive mechanism was that linked rows in the destination
table to corresponding rows resolved by the FROM clause, I found no mention
of this concern, no stated behavior expectations, no rules to govern the
relationship between update-dest and from rows, and only trite, simplistic
examples on msdn. Given that EM doesn't support that construct I had no
easy way to graphically build examples.

I ran across that rule somewhere along the way, it made sense to me, and
since adopting it as my own, I have never seen it fail me.

Sorry I don't have anything more palpable. :-)


-Mark



Anyway, I woule agree that placing updated table as a first one is a bit
more "logical" for a human and could be, probably, recommended for
readability.

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO

CREATE TABLE [dbo].[Table1] (
[A] [int] NOT NULL ,
[X] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[A]
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
GO

CREATE TABLE [dbo].[Table2] (
[A] [int] NOT NULL ,
[X] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table2] ADD
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[A]
) ON [PRIMARY]
GO

Insert Into Table1 ([A]) Values (1)
Insert Into Table1 ([A]) Values (2)
Insert Into Table1 ([A]) Values (3)

Insert Into Table2 Select * From Table1


Update Table1 Set [X]='Text ' + Cast([A] As VarChar)

--this should run without any problem
UPDATE t2
SET t2.X = t1.X
FROM table1 t1 inner join table2 t2 on t1.A = t2.A

Select * From Table1
Select * From Table2
Go
Drop Table Table1
Go
Drop Table Table2
Go

Dmitriy.



.

Relevant Pages

  • Re: Q re Subqueries (Join Predicate) in Access
    ... criteria on table2, you exclude the table 1 records with no table2. ... OR Clause in your criteria. ... as the criteria for the Table1 records not matched in table2 ...
    (microsoft.public.access.queries)
  • Re: Display Changed Records
    ... that last clause should have been: ... -- Also use table1 values to report rows deleted from table2 ... UNION ALL ...
    (microsoft.public.sqlserver.programming)
  • Re: How do I show all in a count query?
    ... I wonder what the query plan would look like ... and that his boss is Bob) is mapped to Assigned_To in multiple records ... clause, but didn't include it in the SELECT clause: ... join won't generate a NULL for any field associated to table1. ...
    (microsoft.public.access.queries)
  • Re: Oracle sql completed with warnings...
    ... from table1 a, table2 b ... One aggregated column ... ... No GROUP BY clause ...
    (comp.databases.oracle.server)
  • Re: Update for multiple tables
    ... I am trying to write a sql script ... I want to update the ckpt_id value in Table1 to 5036 (currently the ... statement will update both correctly and not violate the constraint. ... trying to determine whether using an EXIST clause or an IN clause ...
    (comp.databases.oracle.server)