By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Nested select SQL within an Update query rotated across multiple columns

P: 11
G'day all, thanks in advance for reading.

I've got two tables, one that's full of data regularly externally updated called "tblEmployeeLicences":
Expand|Select|Wrap|Line Numbers
  1. EmployeeID,Name,Licences
  2. 1001,Bill,Drivers
  3. 1001,Bill,Forklift
  4. 1002,Ted,Drivers
  5. 1002,Ted,Forklift
  6. 1002,Ted,Crane
The other table is one that is depended upon by an ID-card printing program, and as such only allows a single row per employee, called "IDProjectData":
EmployeeID,Name,Licence01,Licence02,Licence03...16

I've developed a nested updated/select query to update only the first Licence for each person (it's trivial to get all of them with slight variations on it):
Expand|Select|Wrap|Line Numbers
  1. UPDATE IDProjectData 
  2. RIGHT JOIN [SELECT TOP 1 Licences 
  3. FROM tblEmployeeLicences 
  4. WHERE EmployeeID = [IDProjectData].[EmployeeID] ]. AS Results 
  5. ON IDProjectData.EmployeeID=Results.EmployeeID 
  6. SET IDProjectData.IDWLicence01 = Results.Licences;
The above successfully updates the first licence into the IDProjectData table, but the query prompts the user to enter in the CandidateNo for BOTH tables upon running it.

How do I restructure the query so that it just rolls over them all, updating the 'Licence01' column for each employee?
Or should I be going about this a different way?
Jan 19 '09 #1
Share this Question
Share on Google+
8 Replies


P: 11
Update - the query is now as follows
Expand|Select|Wrap|Line Numbers
  1. UPDATE IDProjectData LEFT JOIN [SELECT TOP 1 Licences
  2. FROM tblEmployeeLicences
  3. WHERE EmployeeID = [IDProjectData].[EmployeeID] ]. AS Results ON IDProjectData.EmployeeID =Results.EmployeeID SET IDProjectData.IDWLicence01 = Results.Licences;
It still prompts for both EmployeeID's but now it updates ALL employees with that particular Employee's first Licence - how do I get it to look at each employees and update their own first Licence?
Jan 19 '09 #2

FishVal
Expert 2.5K+
P: 2,653
Hello.

It looks like you have two copies of the same data which generally is not a good idea.
Since you update [IDProjectData] with values from [tblEmployeeLicences] I guess data primary source is the latter table.
So, why don't you want to use crosstab query to get dynamically pivotted data from [tblEmployeeLicences]?
Jan 19 '09 #3

P: 11
I'm aware that two copies of the same data isn't the best way to do things, but the ID-card software this is designed for uses the [IDProjectData] table, and is very strict about how data is accessed.

I haven't used a crosstab query because I've primarily dealt with SQL up until now - crosstabs and pivots are mostly new to me, and I can't figure out a way to get it to update pivot'd data.
Jan 19 '09 #4

FishVal
Expert 2.5K+
P: 2,653
@modernshoggoth
What does it mean ?
Jan 19 '09 #5

P: 11
The software that accesses the database can only use THAT particular table, and can only use certain cells in certain ways.
What I'm trying to to develop is a way to replicate data from other tables into that table. The way the data arrives into these other tables is also out of my control - therefore I must make a way to put data in this other table via some form of the above update query, or some sort of updating Crosstab or pivot query - and I've no idea where to get started on CT's or Pivots when it comes to updating data rather than simply selecting it.
Jan 19 '09 #6

FishVal
Expert 2.5K+
P: 2,653
  • First of all it is very probable that the software connecting to [IDProjectData] table could connect to crosstab query with the same name. Check it please.
  • Crosstab query being not updateable will give a unupdateable join with [IDProjectData]. So, I suspect it is not possible to update [IDProjectData] this way.
  • [IDProjectData] could be a temporary table created from crosstab query via SELECT INTO.
  • [IDProjectData] could be updated via recordset.
Jan 20 '09 #7

P: 11
Unfortunately it doesn't like working from a selected crosstab query - I've tried that. The program refuses to open, giving nothing in the way of error messages =(

I haven't tried the other possible solutions - I've just solved it now. I've used VBA to parse and execute SQL to be fed back into the main update query. It's slow, but it doesn't need to be fast.

Thanks, guys!
Jan 20 '09 #8

NeoPa
Expert Mod 15k+
P: 31,409
That may be your only solution :(

I'm pretty sure that subqueries in SQL stop it being updatable too.

It's sometimes possible to find sneaky ways around these restrictions, but if you have it processing in code and speed is not a big issue, then that seems like a suitable solution.
Jan 20 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.