UPDATE question. - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

UPDATE question.

Author

Topic

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2008-01-03 : 05:28:01

Hi,

I have two databases. They are equal in the structure. Database1 is just an earlier backup. In the Database2 I have MyTable with a lot of data. Part of this data was damaged (wrong values) so I would like to update Database2.MyTable with values of Database1.MyTable. The problem is that there is no column with unique id in MyTable.

Is there any chance for me to make safe update?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 05:46:21

what is primary key of tables?

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2008-01-03 : 06:12:47

quote:
Originally posted by visakh16

what is primary key of tables?

whole table looks like this:



CREATE TABLE [CDN].[TraSElem](
	[TrS_GIDTyp] [smallint] NULL,
	[TrS_GIDFirma] [int] NULL,
	[TrS_GIDNumer] [int] NOT NULL,
	[TrS_GIDLp] [smallint] NOT NULL,
	[TrS_SubGIDLp] [smallint] NOT NULL,
	[TrS_DstTyp] [smallint] NULL,
	[TrS_DstFirma] [int] NULL,
	[TrS_DstNumer] [int] NULL,
	[TrS_DstLp] [smallint] NULL,
	[TrS_MagTyp] [smallint] NULL,
	[TrS_MagFirma] [int] NULL,
	[TrS_MagNumer] [int] NULL,
	[TrS_MagLp] [smallint] NULL,
	[TrS_ZwrTyp] [smallint] NULL,
	[TrS_ZwrFirma] [int] NULL,
	[TrS_ZwrNumer] [int] NULL,
	[TrS_ZwrLp] [smallint] NULL,
	[TrS_SubZwrLp] [smallint] NULL,
	[TrS_RezTyp] [smallint] NULL,
	[TrS_RezFirma] [int] NULL,
	[TrS_RezNumer] [int] NULL,
	[TrS_RezLp] [smallint] NULL,
	[TrS_TrnTStamp] [int] NULL,
	[TrS_Ilosc] [decimal](11, 4) NULL,
	[TrS_CChTyp] [smallint] NULL,
	[TrS_CChFirma] [int] NULL,
	[TrS_CChNumer] [int] NULL,
	[TrS_CChLp] [smallint] NULL,
	[TrS_Cecha] [varchar](20) COLLATE Polish_CI_AS NULL,
	[TrS_KosztKsiegowy] [decimal](15, 2) NULL,
	[TrS_KosztRzeczywisty] [decimal](15, 2) NULL,
	[TrS_WartoscDostawy] [decimal](15, 2) NULL,
	[TrS_KosztKrajowy] [decimal](15, 2) NULL,
	[TrS_KosztZagraniczny] [decimal](15, 2) NULL,
	[TrS_IloscDPPrzed] [decimal](11, 4) NULL,
	[TrS_KsiegowyDPPrzed] [decimal](15, 2) NULL,
	[TrS_DsFTyp] [smallint] NULL,
	[TrS_DsFFirma] [int] NULL,
	[TrS_DsFNumer] [int] NULL,
	[TrS_DsFLp] [smallint] NULL,
	[TrS_RzeczywistyDPPrzed] [decimal](15, 2) NULL,
	[TrS_WalutaKosztuKrajowego] [varchar](3) COLLATE Polish_CI_AS NULL,
	[TrS_ZlcTyp] [smallint] NULL,
	[TrS_ZlcFirma] [int] NULL,
	[TrS_ZlcNumer] [int] NULL,
	[TrS_ZlcLp] [smallint] NULL,
	[TrS_RezerwacjaZasobu] [smallint] NULL,
	[TrS_WalutaDostawy] [varchar](3) COLLATE Polish_CI_AS NULL,
	[TrS_KrajPoch] [varchar](2) COLLATE Polish_CI_AS NULL,
 CONSTRAINT [TrS_Primary] PRIMARY KEY CLUSTERED 
(
	[TrS_GIDNumer] ASC,
	[TrS_GIDLp] ASC,
	[TrS_SubGIDLp] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Is this mean that TrS_GIDNumer, TrS_GIDLp and TrS_SubGIDLp are creating unique key?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 06:23:46

Yup.exactly. You can do like this

Update t1
SET t1.Val1=t2.val1,
t1.val2=t2.val2,....
FROM [CDN].[TraSElem] t1
INNER JOIN [CDN].[TraSElem] t2
ON t2.[TrS_GIDNumer] = t1.[TrS_GIDNumer]
AND	t2.[TrS_GIDLp] = t1.[TrS_GIDLp]
AND	t2.[TrS_SubGIDLp] = t1.[TrS_SubGIDLp]

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2008-01-03 : 12:58:00

Thanks!

Could you tell me one more thing.. how to select all rows from t1 which have NOT been updated?

Regards.

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 13:03:56

I would bcp out the table on db1, the truncate the table on db2 then bcp the data in

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 13:05:24

Actually, I would dump db1 and just restore it over db2

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources