Complex query help

Databases
1

I need to make a change to a large database and would appreciate some help creating the query to make the change.

I have a table named “exp_channel_titles” and a table named “exp_channel_data”

The “exp_channel_titles” table includes the following fields:

entry_id
channel_id
author_id

The “exp_channel_data” table includes the following fields:

entry_id
field_id_43

What I need to do is find all records in the “exp_channel_titles” table that have a value of 1 for the author_id and a value of 7 for the channel_id and set the author_id equal to the value of field_id_43 from the “exp_chanel_data” table where the entry_id from the “exp_channel_titles” matches the entry_id from the “exp_channel_data” table.

How does the following look?



UPDATE exp_channel_titles, exp_channel_data SET exp_channel_titles.author_id = exp_channel_data.field_id_43 WHERE exp_channel_titles.channel_id == 7 AND exp_channel_titles.author_id == 1 AND exp_channel_titles.entry_id == exp_channel_data.entry_id

Hopefully that makes sense?

Thanks

Paul

p.s It is an expression engine site just in case that is of any relevance.

2

What I need to do is find all records in the “exp_channel_titles” table that have a value of 1 for the author_id and a value of 7 for the channel_id and set the author_id equal to the value of field_id_43 from the “exp_chanel_data” table where the entry_id from the “exp_channel_titles” matches the entry_id from the “exp_channel_data” table.
My head hurts. (:

3

When your head stops hurting, any chance you can let me know if my code is going to kill the site or not? :slight_smile: The site has 16000 members and is due to go live tomorrow so I want to be very careful with my steps.

Thanks Anthony

Paul

4

like your keyboard equals sign key is sticky

:slight_smile:

5

Hehe, the joy of jumping from one programming language to another.

Hmm so apart from the sticky equals sign, would you recommend running that query and crossing my fingers and toes?

6

yes, but on a couple of test tables first :slight_smile:

7

I pressed the button…

And the result was

0 row(s) affected. ( Query took 0.0430 sec )

Hmm any ideas?



UPDATE exp_channel_titles, exp_channel_data SET exp_channel_titles.author_id = exp_channel_data.field_id_43 WHERE exp_channel_titles.channel_id = 7 AND exp_channel_titles.author_id = 1 AND exp_channel_titles.entry_id = exp_channel_data.entry_id AND exp_channel_data.field_id_43 != 'NULL'
8

Thanks r937 - that sounds like a very sensible thing to do.

9 
you don't honestly [B]write [/B]your SQL all on one extremely long single line of code, do you? you're making it awfully difficult for a human being to read it and [size=5]understand[/size] it, you realize, with all that scrolling that has to be done... in any case, i think your problem is right at the end, where it says [COLOR="Red"]something != 'NULL'[/COLOR]... do you actually have the 4-character string 'NULL' stored there, or did you mean NULL, the special keyword that means no value?

:smiley: :smiley:

10

Hmm well I guess I do write it in one big line though I am doing this in phpMyAdmin

With regards the “NULL”, yes the database does contain a value of NULL for field_id_43 for quite a few entries.

Basically I have taken over a site from another company and had to import their database into a CMS called expression engine. I have now realised that a large number of records have been assigned the wrong author_id i.e 1 which is the super admin id when it should have an id specific to a particular member.

To make matters worse it seems the original database didn’t have a value for author_id for a large number of records - I assume this is because the records were added to the site manually as opposed to a member signing up and adding them.

So erm enough of my babbling. I think I need to examine the databases more before I press the button again:)

11

okay, so change something != ‘NULL’ to something IS NOT NULL

12

If I do


SELECT *
FROM `exp_channel_data`
WHERE `field_id_43` != 'NULL'
LIMIT 0 , 30

I get Showing rows 0 - 29 (2,039 total, Query took 0.0011 sec)

If I do



SELECT *
FROM `exp_channel_data`
WHERE `field_id_43` IS NOT NULL
LIMIT 0 , 30

I get Showing rows 0 - 29 (2,097 total, Query took 0.0009 sec)

Hmm how come the difference?

Thanks

Paul

p.s also I did this


SELECT *
FROM `exp_channel_data`
WHERE `field_id_43` IS NULL
LIMIT 0 , 30

Showing rows 0 - 29 (499 total, Query took 0.0010 sec)


SELECT *
FROM `exp_channel_data`
WHERE `field_id_43` = 'NULL'
LIMIT 0 , 30

Showing rows 0 - 29 (58 total, Query took 0.0085 sec)

13

This should get you what you need:


UPDATE exp_channel_titles 
   SET author_id = COALESCE((SELECT field_id_43 
                              FROM exp_channel_data 
                             WHERE exp_channel_titles.entry_id = exp_channel_data.entry_id), author_id) 
 WHERE channel_id = 7 
   AND author_id = 1
14

Hey thanks Dave - I have never come across “COALESCE” before. I will have a read up on it.

Thanks

Paul

15

Dave, will your solution take care of the situation where the value of field_id_43 is NULL? If the value is NULL, I want to leave the author_id as 1 otherwise it will set it to NULL?

16

let me recap for you…

2039 rows != ‘NULL’
2097 rows IS NOT NULL
499 rows IS NULL
58 rows = ‘NULL’

makes sense to me !!! :smiley: :smiley:

what part don’t you understand?

let me make a prediction, the total number of rows in the table will turn out to be 2596

17

Because ‘NULL’ and NULL are not the same. Option one is a specific value, option two is the distinct lack of a value.

18

That’s what I set it up to do, leave the author_id if the subselect returns a null. That’s what the coalesce does - it looks at each pattern within the operator and returns the first non-null in the operator (you can have as many patterns in there as you wish).

Technically in your case, since the coalesce is setting it back to itself, the mySQL interpreter will drop those cases out of the updates and only update those that return a value from the subquery - at least that’s what it did for me when I tested it…

19

Sorry rudy, I was a bit knackered yesterday so couldn’t fathom the reason for the differences.

Anyway Daves code worked perfectly - phew!

Thanks to you all for your assistance.