UPDATE SET on many Fields

[AGP]

Im familiar with the statement:
UPDATE tblSource, tblTarget
SET tblTarget.Field1= tblSource.Field1, tblTarget.Field2= tblSource.Field2
WHERE tblTarget.MyID= tblSource.MyID

However, what if i have a big amount of fields to copy and they strutures of
both tables are exactly the same? is there a way to shorten that SQL
statement or do i have to explicitely write all fields to be copied?

tia

AGP

 

[Allen Browne]

Try the wildcard, i.e. line 2 would be:
SET tblTarget.* = tblSource.*

That kind of construct works fine for INSERTs.

 

[AGP]

in MS SQL via Access I get a "syntax error in UPDATE".
AGP

 

[Allen Browne]

Perhaps someone else will have a suggestion then.

 

[JP Bless]

'For range insert...
INSERT INTO tblY ( Field1, Field2, Field3 )
SELECT tblX.Field1, tblX.Field2, tblX.Field3
FROM tblX Where tblY.Field1 = between 1 and 100

'Single insert...
INSERT INTO tblY ( Field1, Field2, Field3 )
SELECT tblX.Field1, tblX.Field2, tblX.Field3
FROM tblX Where tblY.Field1 = 100

'all data in source table
INSERT INTO tblY ( Field1, Field2, Field3 )
SELECT tblX.Field1, tblX.Field2, tblX.Field3
FROM tblX

 

[JP Bless]

If inserting all fields

INSERT INTO tblY
SELECT tblX.*
FROM tblX;

'For range insert...
INSERT INTO tblY ( Field1, Field2, Field3 )
SELECT tblX.Field1, tblX.Field2, tblX.Field3
FROM tblX Where tblY.Field1 = between 1 and 100

Correction...
Exclude = if using range.

 

[Douglas J. Steele]

For range insert, that's

INSERT INTO tblY ( Field1, Field2, Field3 )
SELECT tblX.Field1, tblX.Field2, tblX.Field3
FROM tblX Where tblY.Field1 between 1 and 100

(in other words, you don't use = with between)

 

[AGP]

thanx for the many tips. however this still requires that you explicitly
write every single field.
what i was after was a statement that would copy all fields from one table
to another table
of the exact same structure. the method demonsrated would require manual
update of all the fields.
that was exactly what i was trying to avoid.

AGP

 

[AGP]

ok that one makes sense but i want to update records in the target table,
not append records.

AGP

 

[Douglas J. Steele]

Sorry, but I don't see having to explicitly write the names of the field as
being a big hardship.

 

[Douglas J. Steele]

On Dec 14, 1:20 pm, "Douglas J. Steele" > For range insert, that's

Surely should be

INSERT INTO tblY ( Field1, Field2, Field3 )
SELECT tblX.Field1, tblX.Field2, tblX.Field3
FROM tblX Where tblX.Field1 between 1 and 100

Yeah, you're right. The erroneous equal sign jumped out, but I didn't look
closely enough to see whether the rest of the statement was correct.

BTW what's the meaning of 'range insert' and 'single insert'? Each
construct returns a set consisting of zero or more rows.

Not my terminology: it's how JP decided to label them. I would assume he's
try to indicate that one will insert a range of rows from the original
table, whereas the other will only insert a single row. (that assumes, of
course, that Field1 is the PK for tblX)

 

[AGP]

point taken. howver the nature of my program is such that the fields might
change.
i was just looking for a generic statement that would copy all fields so
that I didnt have
to go into the routine and update every time the fields change. i appreciate
your help.

AGP

 

[JP Bless]

true... see my correction earlier... = between was typo. Thanks anyway.

 

[James A. Fortune]

Im familiar with the statement:
UPDATE tblSource, tblTarget
SET tblTarget.Field1= tblSource.Field1, tblTarget.Field2= tblSource.Field2
WHERE tblTarget.MyID= tblSource.MyID

However, what if i have a big amount of fields to copy and they strutures of
both tables are exactly the same? is there a way to shorten that SQL
statement or do i have to explicitely write all fields to be copied?

tia

AGP

I am encountering the same problem after management decided to commit
spreadsheet on a form. Think of an Order/OrderDetails arrangement where
each OrderDetail can have a breakout that looks like OrderDetails and
sums back to the original OrderDetail. The actual situation is quite a
bit more complicated since several "orders" can be grouped together and
the entire group must be available simultaneously for editing, not to
mention a few other wrinkles. Groan. For edits I copy the existing
data into local tables and use those. When the user is done editing the
local tables (Add, Edit, Delete), I use the following logic:

1. Update any records in the linked table that match records in the
local table.

2. Append any records to the linked table from the local table that
don't match in the linked table.

3. Delete any records in the linked table that don't match in the local
table.

In this instance a match is based on two foreign keys both matching.
Only one user will be editing a given set of data at a time.

This problem is similar to the BOM problem except that only a few levels
are used. This problem seems to be the kind that benefits from the use
of a class since Albert Kallal recommends classes for complicated
situations. This is certainly that.

I like Jamie's idea of using, say, a tabledef to assist in creating the
SQL string. I was able to save a little space by using:

UPDATE tblLinked AS A INNER JOIN tblLocal AS B ON (A.QEID = B.QEID) AND
(A.QDID = B.QDID) SET A.Field1 = B.Field1, ...;

The Append query also lists each field. My guess is that if fields are
left unspecified, Access will append the fields in the order of the
SELECT statement. I don't want to roll the dice so I think I'm stuck
with listing the fields. If I get a little busy for awhile it's because
I'm beating on this Whak-a-Mole. Here, however, the Whak-a-Mole is not
new bugs popping up, but bizarre feature creep. I typically spend less
than 1% of my time fixing bugs. I get paid for putting in the new
features, but I'd rather be working on other kinds of new functionality.

James A. Fortune
(e-mail address removed)

 

[Douglas J. Steele]

To dynamically generate an SQL statement like

UPDATE tblSource, tblTarget
SET tblTarget.Field1= tblSource.Field1,
tblTarget.Field2= tblSource.Field2
WHERE tblTarget.MyID= tblSource.MyID

use code like:

Dim dbCurr As DAO.Database
Dim tdfSrce As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strSQL As String

strSQL = "UPDATE tblSource, tblTarget SET "

Set dbCurr = CurrentDb()
Set tdfSrce = dbCurr.TableDefs("tblSource")
For Each fldCurr In tdfSrce.Fields
If fldCurr.Name <> "MyID" Then
strSQL = strSQL & "tblTarget." & fldName & _
" = tblSource." & fldName & ", "
End If
Next fldCurr
strSQL = Left$(strSQL, Len(strSQL) - 2)
strSQL = strSQL & "WHERE tblTarget.MyID = tblSource.MyID"

 

[AGP]

that approach is what i ended up with. thanks for the code snippet.

AGP