Can I join TWO tables in one UPDATE query?

Hi all,

I have to update the data in one table but I need to join two tables in the UPDATE query so the correct data gets updated.

I'm not aware of how to update more than one table at one time or in this case pull data from one table that is used to point to another table that needs the update.

I want to update a record but in order to normalize the tables I had to put the "record for content" and the "record for the gym" that uses that content in seperate tables. Now I need to join the two to make an update in the one content table.

Make sense? Help!

Nathan

I think it's possible but have never needed to do it before. You might try asking in one of the SQL forums.

Did I fail to nomarlize the tables correctly? I thought normalizing would make my queries easier...

I have a connect table and a page content table. In a standard select querie I would simply join the two to grab a specific record.

How owuld I update records using the same structure? I thought breaking data down into 'atomic' structures would make things easier.

Nathan

kiteless is right, you'll prob have more luck in a sql forum but see if this helps

http://forums.devshed.com/t36686/s.html&highlight=update+query+join+tables

I've always just done it as two queries within a <cftransaction> block.

I see, but what do those two queries look like? I would appreciate an example as I'm a visual learner.

I can then adapt what you show as an example to what I'm doing.

Nathan

what SQL server are you running? MSSQL? Oracle? MySQL?, PostgreSQL?

I'm not sure I understand...it would just be two update statements, but by wrapping them in <cftransaction> you force it to succeed or fail as a single unit. Like:



<cftransaction>

<cfquery name="query1" dsn="dsn">

UPDATE table1

SET column_name = new_value

WHERE column_name = some_value

</cfquery>

<cfquery name="query2" dsn="dsn">

UPDATE table2

SET column_name = new_value

WHERE column_name = some_value

</cfquery>

</cftransaction>

I received this response from the Database forums.



update table1
inner
join table2
on table1.keyfld = table2.keyfld
set table1.fldx = table2.fldy
where table2.fldz = 'foo'



Does it look like it makes sense?

Nathan

It seems to make sense. The question is really: does it work?