We help IT Professionals succeed at work.

SQL Syntax

222 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...)
 
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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.

Author

Commented:


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
Chris LuttrellSenior Database Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Thanks CG! i made a typo

Author

Commented:
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 ?

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

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.