SQL - copy multiple column values from one row to another with exceptions

Question

I have an Access-compatible database (JET engine, .mdb file) which I manipulate via VBA in Excel. Queries and updates are written in SQL by VBA.

I need to copy across values from one row to another - according to certain conditions. Let me give you an example:

ID  Del     Cat User    Col1    Col1    Col3    Col4    ... Col40
1   False   M   A       xyz     NULL    NULL    xyz     ... xyz
2   True    M   B       NULL    NULL    NULL    NULL    ... xyz
3   False   C   C       NULL    xyz     NULL    NULL    ... xyz
4   False   C   D       xyz     NULL    NULL    xyz     ... xyz
5   False   C   E       NULL    NULL    NULL    NULL    ... NULL
6   True    C   F       xyz     xyz     NULL    NULL    ... xyz
7   False   S   G       xyz     xyz     NULL    xyz     ... NULL
8   False   S   H       NULL    NULL    xyz     NULL    ... NULL
9   False   S   F       NULL    NULL    NULL    NULL    ... NULL
10  False   S   J       xyz     NULL    NULL    xyz     ... NULL

ID is an autoincrement primary key. Del is a boolean indicating whether the record has been marked as deleted (soft-delete) Cat is the category of the record. 3 categories exist: M, C, S.

Let's look at row IDs 6 & 9. They both relate to user "F", but they are in different categories (C and S). However the row in Cat C has been flagged as deleted (Del=True), while S is not deleted (Del=False).

What I want to do is update row 9 (the non-deleted record for user "F"), so that the values of all the substantive data columns (Col1.. Col40) are copied across from row 6. I'd like row 9 to end up looking like this:

9   False   S   F   xyz     xyz NULL    NULL    ... xyz

i.e: identical to row 6 except for the ID; the Del flag (which should remain True for row 6 but False for row 9); and Cat column (which should remain C for row 6 but S for row 9).

I'm aware that other similar questions exist:

... however it's not clear to me exactly how do apply this to what I want, with the exclusion of copying ID, Del and Cat. Additionally, in the examples given in other questions, there are only a handful of columns - but I have over 40. I'm not sure if there's a "blanket" way to copy across all columns with the exclusion of just a couple, or if I have to write all of them out individually (except the ones I don't want copied).

Basically, I know it's going to be something like:

UPDATE [TABLENAME] SET (however to choose all fields except ID, Del and Cat) WHERE User="F";

... however as this is just an example, using User "F", I don't want to have to specify an individual user in hard code. I want this to apply to all users, no matter what the name. Basically, the logic is:

"Look at all rows in the C category with a Deleted=True flag. Then for any deleted C rows, check whether the same User names exist within any other category (whether S or M). For any found, copy the data from the rest of the columns from the deleted record to the non-deleted records - although do not copy the category, deleted or ID values".

I hope this is clear. Grateful for any help from you seasoned SQL people out there!

I guess you need to self-join each deleted row to the corresponding rows you want to copy to (categories S or M with same username as deleted row). As you have that, you can do the UPDATE. I don't think there is an easy way to count all 40 columns... You have to write them all — Alex Zen, Apr 01 '18 at 17:27
@AlexZen - Thanks. Can you please give some sample code in an answer? — Chris Melville, Apr 01 '18 at 17:44

Alex Zen · Accepted Answer · 2018-04-01T18:34:06.437

2

I guess you need to self-join each deleted row to the corresponding rows you want to copy to (categories S or M with same username as deleted row). As you have that, you can do the UPDATE. I don't think there is an easy way to count all 40 columns... You have to write them all.

I'm not familiar with access SQL, but have a look at this update:

UPDATE table1 t
 INNER JOIN table1 u
    ON t.User = u.User
   SET u.Col1 = t.Col1,
       u.Col2 = t.Col2,
       u.Col3 = t.Col3,
       u.Col4 = t.Col4,
       u.Col5 = t.Col5,
       ...
       u.Col38 = t.Col38,
       u.Col39 = t.Col39,
       u.Col40 = t.Col40
 WHERE t.Cat = 'C'
   AND t.Del = 'True'
   AND u.Cat IN ('M', 'S')
   AND u.Del = 'False'

It joins deleted row (t) with one or more corresponding categories (u). At this point, you can update the categories with the values in t.

I'm not sure the syntax is correct, but I hope it can help you anyway

edited Apr 01 '18 at 18:34

answered Apr 01 '18 at 17:55

Alex Zen

906
7
9

1

That's not valid syntax for Access. In Access, an update query never contains `FROM`. Instead, tables and joins are specified directly after the `UPDATE` – Erik A Apr 01 '18 at 18:29
@ErikvonAsmuth thanks, I saw that syntax on a different question, and I thought it was the correct way to do it on access. It should be correct now – Alex Zen Apr 01 '18 at 18:39
Thanks. My work laptop isn't working right now, so I can't check - but will do so on Tuesday and let you know if it works! By the way - I'm not actually using MS-Access at all. It's only an .mdb file, written to directly with SQL over an ADO connection from Excel VBA. Don't know if this makes a difference? – Chris Melville Apr 01 '18 at 19:24
Just tried this - works perfectly! Thank you so much. – Chris Melville Apr 03 '18 at 16:46

SQL - copy multiple column values from one row to another with exceptions

1 Answers1