Solved

SQL Syntax

Posted on 2009-04-01
7
200 Views
Last Modified: 2012-05-06
I have a table on 1 database that i want to recover data so that i can update my other database but with the same table

I want to change the date_received  of  all my payment_numbers that match the data_recieved of the recovered table and update another table from a different database I know what my payment_numbers are

The statement i use to select  my data is and I use the same statement for database A and database B  only thing different on each database is the date_received is all the same with 3/16/2009 and i want to change what to what i get on my recovered table


SELECT      payment_number, date_received
FROM         ar_receipts
where receipt_number IN(105
,106
,229
,307
,330
,333
etc...)
 
0
Comment
Question by:Jiggens
  • 3
  • 3
7 Comments
 
LVL 16

Accepted Solution

by:
Auric1983 earned 475 total points
ID: 24042563
Are the two databases stored on the same SQL Server?

you could try this (select statement only to see if the data view works)

select a.payment_number,a.date_received,b.payment_number,b.date_recieved from database1..dbo.ar_reciepts a inner join database2..dbo.ar_reciepts b on a.payment_number=b.payment_number

This should give you a query with 4 columns 2 from each database.  

Note you need to change database1 and database2 to the names of the two databases in question.

(IF this works we can write an update script to do something similar)
0
 
LVL 1

Author Comment

by:Jiggens
ID: 24042789
So the syntax would be like so

select a.payment_number,a.date_received,b.payment_number,b.date_recieved from Accounting_EOM..dbo.ar_reciepts a inner join p21play..dbo.ar_reciepts b on a.payment_number=b.payment_number


The databases are on the same server.  Did i mess something up ?

i got an error

Msg 7202, Level 11, State 2, Line 1
Could not find server 'Accounting_EOM' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
0
 
LVL 1

Author Comment

by:Jiggens
ID: 24042826


select a.payment_number,a.date_received,b.payment_number,b.date_recieved from database1..dbo.ar_reciepts a inner join database2..dbo.ar_reciepts b on a.payment_number=b.payment_number

What oes database2..dbo.ar_receipts B mean  I also have never seen  the .. in database..dbo.ar_receipts

thanks
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 25 total points
ID: 24043034
you do not need the .. (two dots) in your statement.  Replace
Accounting_EOM..dbo.ar_reciepts with Accounting_EOM.dbo.ar_reciepts
and
p21play..dbo.ar_reciepts with p21play.dbo.ar_reciepts
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24043069
Thanks CG! i made a typo
0
 
LVL 1

Author Comment

by:Jiggens
ID: 24043434
So would the syntax be

UPDATE    p21play.dbo.ar_receipts
SET              date_received = a.date_received
FROM         ar_receipts AS a INNER JOIN
                      p21play.dbo.ar_receipts ON a.payment_number = p21play.dbo.ar_receipts.payment_number
WHERE     (p21play.dbo.ar_receipts.last_maintained_by = 'cjiggens')

or is there another step i am missing ?

0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24043458
That looks correct to me.

You may also want to make sure you have a backup of the table before you do the update

use p21play
select * into ar_reciepts_040109 from ar_reciepts

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now