Rewritten UPDATE query - is it the same?

  • Thread starter Thread starter teddysnips
  • Start date Start date
T

teddysnips

My client has moved their back-end database from Access to SQL Server,
and now the following query doesn't work (Operation must use an
updateable query):

UPDATE tblbookings INNER JOIN tblREFUNDS ON tblbookings.TransFromID =
tblREFUNDS.BookingID SET tblREFUNDS.[MOVED TO NEW BKREF] =
tblbookings.bookingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.TransFromID) Is Not Null));

Both the following variations seem to work, but I can't decide if they
are syntactically interchangeable:

UPDATE tblRefunds, tblBookings
SET [MOVED TO NEW BKREF] = tblbookings.bookingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.TransFromID) Is Not Null)) AND (tblREFUNDS.BookingID =
tblbookings.bookingid);

UPDATE tblREFUNDS AS R SET R.[MOVED TO NEW BKREF] = (SELECT
tblbookings.bookingid from tblbookings where
tblbookings.TransFromID = R.BookingID AND tblbookings.TransFromID IS
Not Null)
WHERE (((R.[MOVED TO NEW BKREF])=0));

I can't really test this because it's live data, and time is
incredibly tight, so you have my copious thanks in advance.

Edward
 
You MUST test this. If time is tight, think what it's going to be like when
you screw up the live data!
 
Back
Top