0

I have an Access Database in which I need to update about 1500 out of 79000 entries in a given table. I've been presented with an Excel spreadsheet of the key ID's for which I need to modify a particular column.

It's basically re-assigning a different "Designer" to 1500 different clients.

I've found a useful tool called WinSQL Lite that allows me to run SQL against the Access database, and then used excel to produce a space-delimited text file with a list of UPDATE statements to change the specific entry, which looks like this:

UPDATE Leads SET Designer = 45 WHERE LeadNumber = 157776 
UPDATE Leads SET Designer = 45 WHERE LeadNumber = 157784 
UPDATE Leads SET Designer = 45 WHERE LeadNumber = 169561

The LeadNumber is different in each case.

Of course SQL isn't designed to run separate UPDATE queries like this, so I tried creating an SQL file with semi-colons to depict a different statement / query on each line, but this doesn't work either.

Now if I copy & paste each statement individually and run it - it does what I need. I just need a method to do this for the 1500 LeadNumbers. My investigation shows a way of nesting the statement into a loop with the different LeadNumbers listed within it but I couldn't work out any way of creating this list from the spreadsheet of ClientNumber's I've been provided.

Sam
  • 7,543
  • 7
  • 48
  • 62
OzPass
  • 11
  • SQL can run multiple `update` queries. What do you mean it is not designed for that? – Gordon Linoff Jun 23 '20 at 21:50
  • "Of course SQL isn't designed to run separate UPDATE queries like this"--why do you say that? – Reg Edit Jun 23 '20 at 22:15
  • I would assume the tool you found doesn't handle multiple batches well. You'd likely have more success importing the excel data to an access table and working inside access to update the data. – Sam Jun 23 '20 at 23:15
  • In ms-access you need use **;** semicolon end of the each update code. – Amirhossein Jun 24 '20 at 03:43
  • In ms-access you need use **;** semicolon end of the each update code. – Amirhossein Jun 24 '20 at 03:43
  • Thank you all, I make the statement regarding SQL not being designed for multiple UPDATE statement based on my experience of not being able to run them in this way, also that all the information I found on the web being that it must be accomplished per the "answer" below... that the statements need to be in a form of routine / loop where the WHERE conditions are fed from a serial list, which I couldn't work how to produce based on the data being given to me in a column in Excel... I tried semicolons but it just said that it had a syntax error with an unexpected character after WHEN. – OzPass Jun 24 '20 at 06:54

1 Answers1

1

You can use IN:

UPDATE Leads
    SET Designer = 4
    WHERE LeadNumber IN (157776, 157784, 169561);

If the values are already in a table, then you can use IN with a subquery:

UPDATE Leads
    SET Designer = 4
    WHERE LeadNumber IN (SELECT ln.LeadNumber FROM LeadNumber as ln);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you I shall try this today. The Leads aren't in a table, as such, rather a spreadsheet but I guess there's no reason I can't create a single table with a single field and just paste these in, right? – OzPass Jun 24 '20 at 06:55
  • @OzPass . . . You can either load the values into a table or construct the query with a giant `in` clause. – Gordon Linoff Jun 24 '20 at 10:40