Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Syntax

Posted on 2009-04-01
7
Medium Priority
?
207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 16

Accepted Solution

by:
Auric1983 earned 1900 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 100 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

604 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