"update" question

[RyanSF07]

Hello,

 

I have a table called "notes" and another table called "video."

 

I'd like to add a row to "video" called "video_notes" -- and then update those rows with data from the "notes" table.

 

"Notes" has 4 rows: id, user_id, video_id, and notes_text.

 

I exported the data from the table notes (sql export). The beginning reads:

 

INSERT INTO `notes` (`id`, `user_id`, `video_id`, `notes_text`) VALUES

(1, 1, 2, '"One buck" is slang for one dollar.).\r\n\r\n\r\n'),

 

and on and on and on...

 

The table "video" has many of these same rows, namely, user_id, and video_id.

 

I'm wondering if I can change "insert" to "update" on that sql and then import it into the "video" table (with new row "video_notes" created.)

 

Would this work:

"UPDATE video SET video.video_notes = '$video_notes_safe' WHERE video.video_id = video_id;

 

I've set up a test database to try this. All coaching appreciated.

 

thanks,

Ryan

[Mchl]

What you're talking about are not rows, but columns. Quite important difference in DB slang.

 

Something like this should work

UPDATE video AS v INNER JOIN notes AS n ON v.video_id = n.video_id SET v.video_notes = n.notes_text

 

Why do you want to move these notes to video table though? This will allow you to only have one note per video, instead of having many-to-one relation.

[RyanSF07]

thanks, Mchl.

 

I'll give that a go and post back.

 

I was thinking I'd have to upload a file with the notes_text, but I see how this make much more sense.

 

one-to-one is the goal for this particular project.

 

thanks again...

[RyanSF07]

Hi Mchl,

 

That worked -- thank you.

 

There is one issue maybe a "select distinct" type of command may fix. Please let me know. Here is the issue.

 

The problem I was having was as you noted before -- in some cases there is a many-to-one relationship. Several notes with the same video_id. They are accidental duplicates.

 

When I ran the command above in the cases where there are these accidental duplicates, the notes_text didn't transfer over. I assume it was because there were a couple instances of notes_text data with the same video_id.

 

Do you understand what I mean? Can it be modified to "in cases where there are several notes_text with the same video_id, select one and update." ?

[Mchl]

Maybe like this:

UPDATE video AS v INNER JOIN (SELECT DISTINCT video_id, notes_text FROM notes) AS n ON v.video_id = n.video_id SET v.video_notes = n.notes_text

[RyanSF07]

hmm.. says 0 rows affected. As it's an "update" shouldn't all instances where video_ids are the same be affected?

[Mchl]

Except for cases where the values in updated fields are already the same.

[RyanSF07]

OK. Got it. In that case -- one more question (thank you so much for your help!)

 

There are some cases where instead of exact duplicates, one instance of notes_text will be empty (but not null -- return character or something) and another instance of notes_text with the same video_id will indeed contain actual text.

 

Can the command be modified to say "select distinct where there is actual text" and insert that into v.video_notes?

 

Don't know if there is our not. Regardless you have been a huge help.

[Mchl]

Sure. You could possibly even figure it out yourself

UPDATE video AS v INNER JOIN (SELECT DISTINCT video_id, notes_text FROM notes WHERE notes_text IS NOT NULL) AS n ON v.video_id = n.video_id SET v.video_notes = n.notes_text