2 Replies - 5787 Views - Last Post: 09 June 2012 - 06:42 PM

#1 Jezzabeanz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-September 11

Update table where values from another table

Posted 08 June 2012 - 12:45 PM

Hi,

I have created a query which updates a customer ID in an order table where the customer's first name and surname are in the customer database.

UPDATE       tblOrder
SET                CustomerID = 
(SELECT CustomerID 
FROM tblCustomer 
WHERE First_Name ='Fname' AND Surname='Sname')
WHERE EXISTS
(SELECT CustomerID 
FROM tblCustomer 
WHERE First_Name ='Fname' AND Surname='Sname')


The reason I have posted this in MS Access is that I aim to use it for VB.Net though I cannot seem to get this query to work.

EDIT: The update values 'Fname' AND 'Sname' are basically place holders as the actual values will be variables.

This post has been edited by Jezzabeanz: 08 June 2012 - 12:47 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Update table where values from another table

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Update table where values from another table

Posted 09 June 2012 - 05:10 PM

Why doesn't it work - error message, wrong results, nothing?

I hope there won't be more than one John Smith in the Customers dataset

This post has been edited by June7: 09 June 2012 - 05:12 PM

Was This Post Helpful? 0
  • +
  • -

#3 Jezzabeanz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-September 11

Re: Update table where values from another table

Posted 09 June 2012 - 06:42 PM

The issue is resolved now, though that was my primary concern, though I found a way around it.

Using the SQL MAX() function, I did 3 queries directly after each other.

The first, was the insertion of the customer information, the second was the SELECT MAX(CustomerID FROM tblCustomer [and stored it into a variable] and the final variable was the insertion of the order information.

I think my approach was flawed due to myself being a PHP developer (primarily) and having access to the UserID from an authenticated session, opposed to finding an alternative means of getting a customerID.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1