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

HawaiiDragonAsked:
Who is Participating?
 
LowfatspreadCommented:
like this ?
UPDATE GradAPP.dbo.Student_INFO
   SET PaymentStatus = 1
 WHERE exists (select ip from dbo.tblpayment as a 
                where a.Successfull = 1 
                 and a.ip=GradAPP.dbo.Student_INFO.ip
                 and a.paymenttype=2)
   AND PaymentStatus = 0

Open in new window

0
 
HawaiiDragonAuthor Commented:
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

0
 
HawaiiDragonAuthor Commented:
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!!!
0
 
HawaiiDragonAuthor Commented:
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!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.