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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
167 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
167 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

549 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
167 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  
 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.22 seconds. Powered By: Snitz Forums 2000