Update one field by selecting values from two other tables, very confused here

[beejeebers]

I am using this: MySQL client version: 4.1.22

 

Here's the tables:

 

kw_log

-------

rotation_name

network_offer

 

rotations

---------

rotation_name

offer_id

 

offers

-----

id

network_offer

 

I screwed up my initial table design in the kw_log table and need to run an update statement to fill in the value for the rotation_name in the kw_log table.

 

The rotation_name is in the rotations table, along with the offer_id which is the same as the id in the offer table. The only way I can see to figure out what rotation_name to base the field value on is to match the network_offer values in the offers and kw_log tables. (Hope this makes sense as I'm lost on how to do this...)

 

I think it's possible using joins but am clueless here...any help is appreciated.  I have tried the following so far:

 

UPDATE kw_log SET rotation_name = rotation_name_to_update WHERE (

SELECT rotations.rotation_name AS rotation_name_to_update

FROM rotations, offers

WHERE rotations.offer_id = offers.id

)

 

Didn't work, it said:

 

#1054 - Unknown column 'rotation_name_to_update' in 'field list'

 

I'm just not sure what to set the field to...

 

so I tried this:

 

UPDATE kw_log SET rotation_name = ( SELECT rotations.rotation_name AS rotation_name_to_update

FROM rotations, offers

WHERE rotations.offer_id = offers.id )

 

that didn't work either it said:

 

#1242 - Subquery returns more than 1 row

 

Which is true, but I still want to update that rotation_name field in the kw_log table...

 

I've also tried this:

 

UPDATE kw_log SET rotation_name = rotations.rotation_name

WHERE rotations.offer_id = offers.id

 

But that didn't work, it said:

 

#1109 - Unknown table 'rotations' in where clause

[fenway]

It would be easier to use a multi-table update rather than a subquery... your inner alias will never be visible to the outer query, hence the eorr message.

[beejeebers]

UPDATE kw_log,

rotations,

offers SET kw_log.rotation_name = rotations.rotation_name WHERE rotations.offer_id = offers.id AND kw_log.network_offer = offers.network_offer

 

Is that what you mean?

[fenway]

I prefer JOIN syntax, but yes... I'd verify with an equivalent select query to make sure it will do what you want first.