Help doing an update with two joins

[busboy]

It errors out complaining about the GROUP BY. This query basically makes a change to testimonials that do not have an associated trademarked product topic tag.

update
	testimonials a
left join
	testimonialTopics b
on
    a.testimonialID = b.testimonialID
left join
	topics c
on
	b.topicID = c.topicID
set
	a.title = a.compliantTitle, a.body = a.compliantBody, a.keywords = a.compliantKeywords
where
	a.approved = 'Yes'
	and a.compliantTitle is not null
group by
	a.testimonialID
having
	max(c.product) < 'Yes'
limit
	10

Thank you.

[WebMachine]

@busboy

having max(c.product) < ‘Yes’

This statement comes right after the group by clause. Correct me if I’m wrong, but I thought max was used for numerical comparisons. I don’t understand the < 'Yes'

[busboy]

I don’t fully understand it either, as someone helped me structure the original SELECT query. But it works good. Now I’m just trying to tweak it so I can modify the results of that select query.

[Mittineague]

@busboy

complaining about the GROUP BY

What is the actual complaint?

[busboy]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘set t.title = t.compliantTitle, t.body = t.compliantBody, t.keywords = t.complia’ at line 11

Error code 1064.

[r937]

@WebMachine

Correct me if I’m wrong, but I thought max was used for numerical comparisons.

sorry, yes, you’re wrong… MAX() can apply to strings… ‘Yes’ is greater than ‘No’

[r937]

@busboy

near ‘set

so clearly, SET doesn’t work at that point in the query

note the manual says you cannot use ORDER BY or LIMIT in a joined update

but now to the more salient questions i have for you

1. why GROUP BY? what exactly are the rows being grouped

2. why LEFT JOINs? you want to update testimonials that don’t have matching topics? note that your attempt to have max(c.product) < 'Yes' suggests that you want inner joins

3. most importantly, why are you updating testimonials colulmns from other columns in the same row?

[busboy]

Good to see you’re still around r937. I haven’t seen you in a while and thought you were no longer with us.

It’s grouped by testimonialID because otherwise it would produce lots of rows for each testimonialID.

Yes, the goal is to update testimonials that do not have a very specific topic assigned to it…one that it identified as a trademarked product. Thousands of testimonials may have the general topic of lemon-oil, but I’m trying to find those that don’t yet have a trademarked product topic like purification-oil.

I’m updating columns from other columns in the same row because I’m basically backing up data before changes are made by one of my editor employees.

I look forward to the wisdom offered in your next reply. Thanks!

[r937]

@busboy

Good to see you’re still around r937. I haven’t seen you in a while and thought you were no longer with us.

i’ve been here twice a day (or more) for over ten years… you?

the volume of threads has fallen off dramatically (especially reply-worthy ones), so maybe that’s why

@busboy

It’s grouped by testimonialID because otherwise it would produce lots of rows for each testimonialID.

that makes no sense to me in an UPDATE query

@busboy

Yes, the goal is to update testimonials that do not have a very specific topic assigned to it…one that it identified as a trademarked product. Thousands of testimonials may have the general topic of lemon-oil, but I’m trying to find those that don’t yet have a trademarked product topic like purification-oil.

wut?

@busboy

I’m updating columns from other columns in the same row because I’m basically backing up data before changes are made by one of my editor employees.

that’s not how backups are normally done, tho

@busboy

I look forward to the wisdom offered in your next reply. Thanks!

wisdom? you may be waiting for a while…

;o)