SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 PURE NEWBIE:SQL Query Update/replace
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jrobin747
Starting Member

USA
48 Posts

Posted - 06/07/2013 :  11:52:46  Show Profile  Reply with Quote
I am clueless to writing queries. I am learning what doesn't work. lol

I believe my question is simple. However I cannot make the query work.

I have two databases.
dbo.DatalinkMerchantAccount_TEMP
dbo.DatalinkMerchantAccount_TEMP_old

They both have the same fields
MerchantID, DBA, BankAccountNum, BankTransitNum

I want to update/replace the current values in dbo.DatalinkMerchantAccount_TEMP_old with dbo.DatalinkMerchantAccount_TEMP

All the rows in MerchantID were filled with one value when I tried this:
UPDATE dbo.DatalinkMerchantAccount_TEMP_old
SET dbo.DatalinkMerchantAccount_TEMP_old.MerchantID = dbo.DatalinkMerchantAccount_TEMP.MerchantID
FROM dbo.DatalinkMerchantAccount_TEMP

Got and error when I tried the following
Select *
into dbo.DatalinkMerchant_TEMP_old
From dbo.DatalinkMerchantAccount_TEMP
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'DatalinkMerchant' in the database.

Update dbo.DatalinkMerchant_TEMP_old
Select *
From dbo.DatalinkMerchantAccount_TEMP
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Select'.

Update dbo.DatalinkMerchant_TEMP_old
SELECT MerchantID, DBA, BankAccountNum, BankTransitNum
From dbo.DatalinkMerchantAccount_TEMP
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Select'.

I'm trying different things and I'm not success. Thanks

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 06/07/2013 :  12:04:18  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote


UPDATE dbo.DatalinkMerchantAccount_TEMP_old
SET dbo.DatalinkMerchantAccount_TEMP_old.MerchantID =(select MerchantID from dbo.DatalinkMerchantAccount_TEMP ) ;

kmkmmm
Go to Top of Page

jrobin747
Starting Member

USA
48 Posts

Posted - 06/07/2013 :  12:09:50  Show Profile  Reply with Quote
quote:
Originally posted by pascal_jimi



UPDATE dbo.DatalinkMerchantAccount_TEMP_old
SET dbo.DatalinkMerchantAccount_TEMP_old.MerchantID =(select MerchantID from dbo.DatalinkMerchantAccount_TEMP ) ;

kmkmmm



Thanks, however I got an error
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.DatalinkMerchantAccount_TEMP_old'.
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 06/07/2013 :  12:37:55  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
create table t1(name nvarchar(20), age int);
create table t2(name nvarchar(20), age int);

insert into t1(name, age) values('ivanov',35);
insert into t1(name, age) values('petrov',45);
insert into t1(name, age) values('sidorov',55);

insert into t2(name) values('ivanov');
insert into t2(name) values('petrov');
insert into t2(name) values('sidorov');
go

select*from t1
select*from t2
go

UPDATE t2
SET t2.age = (
SELECT t1.age
FROM t1
WHERE t1.name = t2.name)
go
select*from t2

kmkmmm
Go to Top of Page

jrobin747
Starting Member

USA
48 Posts

Posted - 06/07/2013 :  12:49:46  Show Profile  Reply with Quote
quote:
Originally posted by pascal_jimi

create table t1(name nvarchar(20), age int);
create table t2(name nvarchar(20), age int);

insert into t1(name, age) values('ivanov',35);
insert into t1(name, age) values('petrov',45);
insert into t1(name, age) values('sidorov',55);

insert into t2(name) values('ivanov');
insert into t2(name) values('petrov');
insert into t2(name) values('sidorov');
go

select*from t1
select*from t2
go

UPDATE t2
SET t2.age = (
SELECT t1.age
FROM t1
WHERE t1.name = t2.name)
go
select*from t2

kmkmmm



I think you replied to the wrong post
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/07/2013 :  13:43:08  Show Profile  Reply with Quote
I believe PascalJimi is trying to provide an example for you.
You can update your tables in couple of ways:
1. If you want to keep part of the data in the dbo.DatalinkMerchantAccount_TEMP_old table then you can do something like this:


UPDATE T2 SET DBA = T1.DBA, 
	BankAccountNum = T1.BankAccountNum,
	BankTransitNum = T1.BankTransitNum 
  FROM  dbo.DatalinkMerchantAccount_TEMP_old AS T2 JOIN dbo.DatalinkMerchantAccount_TEMP
 AS T1 ON T1.MerchantID = T2.MerchantID



2. If you don't care to keep any of the data in the table dbo.DatalinkMerchantAccount_TEMP_old table then you can do something like this:



TRUNCATE TABLE dbo.DatalinkMerchantAccount_TEMP_old; -- WARNING THIS COMMAND DELETES ALL THE DATA FROM THIS TABLE!!!
INSERT INTO dbo.DatalinkMerchantAccount_TEMP_old(MerchantID, DBA, BankAccountNum, BankTransitNum)
	SELECT MerchantID, DBA, BankAccountNum, BankTransitNum FROM dbo.DatalinkMerchantAccount_TEMP;
Go to Top of Page

jrobin747
Starting Member

USA
48 Posts

Posted - 06/07/2013 :  16:23:48  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

I believe PascalJimi is trying to provide an example for you.
You can update your tables in couple of ways:
1. If you want to keep part of the data in the dbo.DatalinkMerchantAccount_TEMP_old table then you can do something like this:


UPDATE T2 SET DBA = T1.DBA, 
	BankAccountNum = T1.BankAccountNum,
	BankTransitNum = T1.BankTransitNum 
  FROM  dbo.DatalinkMerchantAccount_TEMP_old AS T2 JOIN dbo.DatalinkMerchantAccount_TEMP
 AS T1 ON T1.MerchantID = T2.MerchantID



2. If you don't care to keep any of the data in the table dbo.DatalinkMerchantAccount_TEMP_old table then you can do something like this:



TRUNCATE TABLE dbo.DatalinkMerchantAccount_TEMP_old; -- WARNING THIS COMMAND DELETES ALL THE DATA FROM THIS TABLE!!!
INSERT INTO dbo.DatalinkMerchantAccount_TEMP_old(MerchantID, DBA, BankAccountNum, BankTransitNum)
	SELECT MerchantID, DBA, BankAccountNum, BankTransitNum FROM dbo.DatalinkMerchantAccount_TEMP;




Using your first example I wrote
UPDATE dbo.DatalinkMerchantAccount_TEMP_old SET DBA = dbo.DatalinkMerchantAccount_TEMP.DBA,
BankAccountNum = dbo.DatalinkMerchantAccount_TEMP.BankAccountNum,
BankTransitNum = dbo.DatalinkMerchantAccount_TEMP.BankTransitNum
FROM dbo.DatalinkMerchantAccount_TEMP_old AS dbo.DatalinkMerchantAccount_TEMP_old JOIN dbo.DatalinkMerchantAccount_TEMP
AS dbo.DatalinkMerchantAccount_TEMP ON dbo.DatalinkMerchantAccount_TEMP.MerchantID = dbo.DatalinkMerchantAccount_TEMP_old.MerchantID

and I got this error
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.

Why did I use FROM for the old database when I want to put info inside from the 1st database
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 06/08/2013 :  02:22:47  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
If you have not solved this problem

how to write clearly and has ties TABLES
for example
On t1phone = t2phone

and

What do you want modify


kmkmmm
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000