Update syntax 1

[Recordsetclown]

I want to change the listingsdb_featured value for records returned by this query to 'yes'. I was hoping to use update, but haven't found a working syntax. Can anyone assist?

SELECT listingsdb_featured from default_en_listingsdb as r1
LEFT OUTER JOIN default_classlistingsdb as r4
ON r4.listingsdb_id = r1.listingsdb_id
AND r4.class_id = '1'
LEFT OUTER JOIN default_en_listingsdbelements as r2
ON r2.listingsdb_id = r1.listingsdb_id
AND r2.listingsdbelements_field_name = 'price'
AND r2.listingsdbelements_field_value > '70000'
LEFT OUTER JOIN default_en_listingsdbelements as r3
ON r3.listingsdb_id = r1.listingsdb_id
WHERE r3.listingsdbelements_field_name = 'MLSID'
AND r3.listingsdbelements_field_value = 'idnum'

 

[r937]

which table contains the column you want to update?

also, please note, your WHERE conditions on r3 effectively turn the last LEFT OUTER JOIN into an inner join, so you should really code it that way

furthermore, if the field you want to update is in r1, then you don't need the other two LEFT OUTER JOINs at all in the update

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Recordsetclown]

The column to be updated is in r1. There is one row per listing in both r1 and r4, and many rows with various field names in the other table.

 

[r937]

yeah but those other tables are in a LEFT OUTER JOIN, so the update wouldn't care if there were actually any rows there, if you see what i mean

did you use LEFT OUTER JOINs on purpose, or ... ?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Recordsetclown]

Yes, or ... Substituting join in all instances finds few rows - I thought too few - but they meet all the criteria, where the other appears to include rows that meet some but not all.

 

[r937]

back up your data first, then try this --

UPDATE default_en_listingsdb as r1
INNER 
  JOIN default_classlistingsdb as r4
    ON r4.listingsdb_id = r1.listingsdb_idA
   AND r4.class_id = '1'
INNER 
  JOIN default_en_listingsdbelements as r2
    ON r2.listingsdb_id = r1.listingsdb_id
   AND r2.listingsdbelements_field_name = 'price'
   AND r2.listingsdbelements_field_value > '70000'
INNER 
  JOIN default_en_listingsdbelements as r3
    ON r3.listingsdb_id = r1.listingsdb_id
   AND r3.listingsdbelements_field_name = 'MLSID'
   AND r3.listingsdbelements_field_value = 'idnum' 
SET    r1.listingsdb_featured = 'yes'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Recordsetclown]

Thanks very much. I shouldn't have quoted the price value - that seems to be why I got just a few records. The update worked just as I wanted.