Update One Table Based On Value In Another Table

Databases
1

Friends:

I am a mySQL novice. I tried to search for a similar question to the one I have, but I can’t find anything. I suspect it’s because I’m such a novice that I don’t really even know what to search on, because my question doesn’t seem that complicated.

I’m trying to use phpMyAdmin to update a field in one table based on the value of a field in another table.

For example, if I have two tables - weblogtable and categorytable - which look like this:

weblogtable:


post          channel
  1                 4
  2                 4
  3                 3
  4                 4
  5                 2
  6                 3
  7                 4

categorytable:


post          category
  1                60
  2                60
  3                61
  4                62
  5                59
  6                55
  7                57

For every post that has a channel value of 4, I want to update that post’s category to be 70.

I did some searching on the web and thought I had found something. That led me to try this query:


UPDATE categorytable t1 JOIN weblogtable t2 ON t1.post = t2.post
SET t1.category = 70
WHERE t2.channel = 4

but it didn’t appear to work.

Is there a way to do what I want to do?

Thanks in advance for any help anyone can offer - I really appreciate it.

Sincerely,

Frank Johnson

2

It looks like row 2367 had its category changed to 70.

Thanks,
Frank

3

so in the first post, when you said “but it didn’t appear to work” it actually did work, right?

4

that’s interesting, so there’s not a lot of investigation left to do :slight_smile:

which post is it, and did it actually have its category changed to 70?

5

run the subquery by itself, and see what it produces

then manually take a look into the exp_category_posts table, to see if there are any rows with an entry_id that was returned by the subquery

6

in mine neither

we need the sticky clarified

obfuscating table and column names causes way more trouble than it purports to avoid

7

I checked that sticky, and your right :eek:

  1. Keep Sensitive Information Out
Make sure your code does not include such things as Passwords, Email Addresses, Folder/File names/paths, [B]Database table/field names[/B] etc. For your security, replace them with ***** or with something generic like mypassword, mytable. If you're afraid someone might see it, don't post it! The Internet is Forever.

Passwords and email addresses, of course, but database table/field names are not sensitive data. At least not in my opinion.

8

The result is this:


COUNT(*)
       1
9

I ran the subquery, which I assume means this part of the query:


SELECT t2.entry_id FROM exp_weblog_data t2 WHERE weblog_id = 4

and it returned the eight records I would expect - the only records with a weblog_id value of 4.

Thanks,
Frank

10

okay, that worked fine

now please run this –

SELECT COUNT(*) 
  FROM exp_category_posts t1
 WHERE t1.entry_id IN ( 1,2,3 )

except replace 1,2,3 with the list of ids you got from running the subquery by itself

11

Yes, I suppose so. What’s interesting - and what I’m not sure how to handle now - is that the eight posts that did not have their category changed are not in the exp_category_posts table (presumably because, as I see now, they have no category at all).

But I still need to change their weblog_id. Is there a way using queries to compare the entry_id list in each of the two tables (so I can know which rows don’t appear in exp_category_posts)?

12

Why?

but I’m pretty sure I used the correct names when I tried the query.

Who knows. We don’t for sure, since you didn’t post them here :slight_smile:

13

Thanks Dave!

So here are the actual table and field names that I referred to in my original post:

For example, if I have two tables - weblogtable and categorytable - which look like this:

exp_weblog_data:


entry_id      weblog_id
  1                 4
  2                 4
  3                 3
  4                 4
  5                 2
  6                 3
  7                 4

exp_category_posts:


entry_id       cat_id
  1                60
  2                60
  3                61
  4                62
  5                59
  6                55
  7                57

By way of reminder, here’s what I want to do: for every entry_id that has a weblog_id of 4, I want to update that entry’s cat_id to be 70.

And here’s the query I used:


UPDATE exp_category_posts t1
SET cat_id = 70
WHERE t1.entry_id IN (SELECT t2.entry_id FROM exp_weblog_data t2 WHERE weblog_id = 4)

In phpMyAdmin, I ran this query while “looking at” the exp_category_posts table, but it returned zero rows affected.

One thing that didn’t seem logical to me in the query is that it would seem like the t2 alias is being used before its defined, but that may be my “novice status” showing through! :slight_smile:

Thanks again to everyone for your help so far!

Frank

14

Thanks Kalon. Unfortunately, it didn’t seem to work. Zero rows were affected. I didn’t use the actual table and field names, but I’m pretty sure I used the correct names when I tried the query.

Frank

15

I’ve clarified the stickied thread. I know what the intent was, but in this forum, it can cause more harm than good.

16

It seemed a little funny to me too, but being a novice, I figured maybe there was something I didn’t understand. And I didn’t want my first post in the forum to be a violation! :slight_smile:

Frank

17
Off Topic:

I already wrote a PM to mittinigue about it. And I also wrote that you might feel the same way… :wink:

18

Thanks guido2004. I didn’t use the actual names because the thread at the beginning of the forum (“Important: New to the Program Your Site forums? Please read this.”) says not to.

Frank

19

Try

 
update categorytable t1
set category = 70 
where t1.post in (select t2.post from weblogtable t2 where channel = 4)