Skip to main content
A
Anonymous
July 28, 2008
Question

Update table 1 with data from Table 2?

  • July 28, 2008
  • 4 replies
  • 1094 views
Ok I successfully populated my new table with data from another table.

Now, I need to add additional data to the new (multiple) records (UPDATE) from yet another table.

I know how to kick off an update action for one record using a passed key field from a form or link (URL), but I keep getting syntax errors trying to complete the data transfer with:

<cfquery name="updatedata" datasource="somedata">
UPDATE new_table
FROM old_table
SET vendor_address1 = "Street1",
vendor_address2 = "Street2",
vendor_city = "City",
vendor_state = "StateID",
vendor_zip = "Zip",
vendor_zip4 = "Zip4",
vendor_tele = "Phone",
vendor_fax = "Fax"

WHERE old_location_ID = "PhysicalLocationID"

</cfquery>

I've tried several variations of this- sorry but what am I missing?

Thanks in advance.

newportri
This topic has been closed for replies.

4 replies

P
paross1
Participating Frequently
July 29, 2008
What database are you using?

Phil
A
Anonymous
July 29, 2008
Phil:

You are responding to both my posts- yesterday when I tried the UPDATE method, and today while I'm attempting the CREATE method- for now, the DB is Access.
P
paross1
Participating Frequently
July 29, 2008
How about something like this?

UPDATE newtable n
INNER JOIN oldtable o ON o.PhysicalLocationID = n.old_location_ID
SET n.vendor_address1 = o.Street1,
n.vendor_address2 = o.Street2,
n.vendor_city = o.City,
n.vendor_state = o.StateID,
n.vendor_zip = o.Zip,
n.vendor_zip4 = o.Zip4,
n.vendor_tele = o.Phone,
n.vendor_fax = o.Fax

Phil
M
musicmp3
Participating Frequently
July 29, 2008
Its a nice and simple query.
For music visit http://mp3bravo.com
D
Dan_Bracuk
Inspiring
July 28, 2008
this could be db specific, but when I do that sort of thing using redbrick, I use this syntax

update t1
set field1 = t2.field1
, etc

from table1 t1 join table2 t2 on something
where etc
D
Dan_Bracuk
Inspiring
July 28, 2008
double quotes are always bad.

A
Anonymous
July 28, 2008
Dan:

Thanks- but I'm still getting syntax errors:

<cfquery name="updatedata" datasource="somedatabase">
UPDATE newtable
FROM oldtable
SET vendor_address1 = Street1,
vendor_address2 = Street2,
vendor_city = City,
vendor_state = StateID,
vendor_zip = Zip,
vendor_zip4 = Zip4,
vendor_tele = Phone,
vendor_fax = Fax

WHERE old_location_ID = PhysicalLocationID

</cfquery>

Wouldn't the values that I need to update all the records with need to be from a additional query (e.g. something like:

<cfquery name="getdata" datasource="somedatabase">
SELECT PhysicalLocationID, Street1, Street2, City, StateID, Zip, Zip4, Phone, Fax, old_location_ID
FROM oldtable, newtable
WHERE oldtable.PhysicalLocationID = newtable.old_location_ID
</cfquery>

<cfoutput query="getdata">


<cfquery name="updatedata" datasource="vendors.mdb">
UPDATE vendor_new
SET vendor_address1 = '#Street1#', etc.?

Sorry- but this is the last "hurdle" I need to jump to bring this whole thing together...


Thanks!

newportri
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices