Newbie queston - update T1 from T2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Newbie queston - update T1 from T2

Author

Topic

sogorman
Starting Member

5 Posts

Posted - 2010-12-19 : 16:25:30

I have two tables and I want to update T1 with all the columns and data from T2 (same table structure just different data) where the column "Unique" matches in both T1 and T2 but I can't quit get my syntax down.

I am using the Update command but is there an easy way of writing it where I don't have to set all of the columns (there are bout 20) from T1 = T2

Thanks for all the help!

Sean

maevr
Posting Yak Master

169 Posts

Posted - 2010-12-20 : 01:05:41

update a set
a.col1 = b.col1
,a.col2 = b.col2
from tbl1 a inner join tbl2 b
on a.id = b.id

sogorman
Starting Member

5 Posts

Posted - 2010-12-20 : 08:19:18

Maevr

Thanks for helping me out, I appreciate it! Can you take a look at my query below? I keep getting the error below.

Thanks again all for lending me some of your knowledge!

Error:

Server: Msg 107, Level 16, State 3, Line 6
The column prefix 'PlayTableHolding' does not match with a table name or alias name used in the query.

Query:

SELECT * INTO PlayTableHolding FROM PlayTable WHERE ID like '%HD_%'

Update PlayTableHolding
SET HasOffset = 1

update PlayTable set
PlayTable.Number = PlayTableHolding.Number
,PlayTable."Unique" = PlayTableHolding."Unique"
,PlayTable."ID" = PlayTableHolding."ID"
,PlayTable.Title = PlayTableHolding.Title
,PlayTable.ScheduledTime = PlayTableHolding.ScheduledTime
,PlayTable.ActualTime = PlayTableHolding.ActualTime
,PlayTable.Status = PlayTableHolding.Status
,PlayTable.EventType = PlayTableHolding.EventType
,PlayTable.Source = PlayTableHolding.Source
,PlayTable.SwitchDuration = PlayTableHolding.SwitchDuration
,PlayTable.Transistion = PlayTableHolding.Transistion
,PlayTable.TransistionDuration = PlayTableHolding.TransistionDuration
,PlayTable.TrigType = PlayTableHolding.TrigType
,PlayTable.TrackUnique = PlayTableHolding.TrackUnique
,PlayTable.HasOffset = PlayTableHolding.HasOffset
,PlayTable."Play Duration" = PlayTableHolding."Play Duration"
,PlayTable.OffsetTrack = PlayTableHolding.OffsetTrack
,PlayTable.AirDate = PlayTableHolding.AirDate
,PlayTable.OverrideDuration = PlayTableHolding.OverrideDuration
,PlayTable.TapeLoaded = PlayTableHolding.TapeLoaded
,PlayTable.TrafficDuration = PlayTableHolding.TrafficDuration
,PlayTable.TrafficHitTime = PlayTableHolding.TrafficHitTime

from PlayTable a inner join PlayTableHolding b
on PlayTable."Unique" = PlayTableHolding."unique"

Kristen
Test

22859 Posts

Posted - 2010-12-20 : 08:24:42

You have aliased PlayTableHolding as "b" by using:


join PlayTableHolding b

so everywhere you have referenced "PlayTableHolding." needs to therefore be changed to "b."

sogorman
Starting Member

5 Posts

Posted - 2010-12-20 : 08:58:41

Thanks Kristin!

Works like a charm!

I'll sure I'll be back with more questions :)

sogorman
Starting Member

5 Posts

Posted - 2010-12-20 : 09:37:25

Ok hopefully last question....

I have added some lines to my query in where i am attempting to inset data into a table and I am getting the error...

Server: Msg 8101, Level 16, State 1, Line 60
An explicit value for the identity column in table 'PlayTableOffset' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Here is my query....

INSERT INTO PlayTableOffsetHolding
(OffsetTrack)
SELECT PlayTableHolding.OffsetTrack
FROM PlayTableHolding

Update PlayTableOffsetHolding
SET Offset='+00:00:10:00', AutoInc ='9999999', "Action" = 'RunMacro', "Device" = 'Macro', "Option1" = 'Test'

INSERT INTO PlayTableOffset SELECT * FROM PlayTableOffsetHolding

Should I add the line

SET IDENTITY_INSERT PlayTableOffset ON

after my Insert INSERT INTO PlayTableOffset SELECT * FROM PlayTableOffsetHolding

Thanks!

Kristen
Test

22859 Posts

Posted - 2010-12-20 : 12:30:35

SET IDENTITY_INSERT PlayTableOffset ON

INSERT INTO PlayTableOffset SELECT * FROM PlayTableOffsetHolding

SET IDENTITY_INSERT PlayTableOffset OFF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources