asked on

sql update in a select across a Join

Hello All.

I am trying to do an update on one table when there is a matching record in another table on a diffrent database below is my code. She builds find but does not work ... any IDEAS?

SELECT     a.*, b.*
                           FROM         dbo.tblPayment AS a INNER JOIN
                                                  GradAPP.dbo.Student_INFO AS b ON a.IP = b.IP
                           WHERE     (a.PaymentType = 2)
                                                      UPDATE    b
                                                       SET              b.PaymentStatus = 1
                                                       WHERE     (a.Successfull = 1) AND (b.PaymentStatus = 0)

Open in new window

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HawaiiDragon

ASKER

This is what I have tried to no avail

error
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Wdb_CCPayment.dbo.tblPayment.Successful" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Wdb_CCPayment.dbo.tblPayment.IP" could not be bound.

UPDATE GradApp.dbo.STUDENT_INFO
sET Gradapp.dbo.student_Info.PaymentStatus = 1

WHERE (Wdb_CCPayment.dbo.tblPayment.Successful = 1) and (Gradapp.dbo.student_Info.pAYMENTsTATUS = 0) and (Gradapp.dbo.student_Info.IP = Wdb_CCPayment.dbo.tblPayment.IP)

Open in new window

HawaiiDragon

ASKER

UPDATE GradAPP.dbo.Student_INFO
SET PaymentStatus = 1
WHERE exists (select ip from WDB_CCPayment.dbo.tblpayment as a
where a.Successful = 1
and a.ip=GradAPP.dbo.Student_INFO.ip
and a.paymenttype=2)
AND PaymentStatus = 0

Low fat you rock!!!

HawaiiDragon

ASKER