Please Help - urgent request Pt II

Now I have another SQL query for MySQL I can't figure out!! This is
overwhelming me completely and I also must have this figured out today
and I can't figure it out!!

UPDATE student_db.student
SET has_letter1 = (
SELECT i.letter1 FROM olddb.student i, student_db student s WHERE
s.unique_key = i.unique_key
)

This query produces the following error:

[Quote]You can't specify target table 'student' for update in FROM
clause[/Quote]

I can't figure this one out and am required to do so rather urgently
(as in before COB today)

Thanx
Phil

Re: Please Help - urgent request Pt II

wrote in message
news:1138642668.508154.246590@g43g2000cwa.googlegroups.com.. .
> Now I have another SQL query for MySQL I can't figure out!! This is
> overwhelming me completely and I also must have this figured out today
> and I can't figure it out!!

Didn't we go through multi-table updates the other day?
I even gave you the link to the page in the documentation that mentions it:
http://dev.mysql.com/doc/refman/5.0/en/update.html
Did you read that page?

> UPDATE student_db.student
> SET has_letter1 = (
> SELECT i.letter1 FROM olddb.student i, student_db student s WHERE
> s.unique_key = i.unique_key
> )

UPDATE student_db.student as news, olddb.student as olds
SET news.has_letter1 = olds.letter1
WHERE news.unique_key = olds.unique_key

> This query produces the following error:
>
> [Quote]You can't specify target table 'student' for update in FROM
> clause[/Quote]

MySQL has a limitation that you can't read from and write to a given table
in the same query.

> I can't figure this one out and am required to do so rather urgently
> (as in before COB today)

Project deadlines are your manager's responsibility. A manager's
responsibility is to make sure the workers have what they need to finish the
job -- including time. If he/she hasn't given enough time to accomplish the
task, it's not _your_ toes that should be on fire.

I am genuinely alarmed that you are doing these changes without any
preperatory test runs *or* validation tests. I guess that's the government
for you. :-(

Regards,
Bill K.

Re: Please Help - urgent request Pt II

See below, thanx

Bill Karwin wrote:
> wrote in message
> news:1138642668.508154.246590@g43g2000cwa.googlegroups.com.. .
> > Now I have another SQL query for MySQL I can't figure out!! This is
> > overwhelming me completely and I also must have this figured out today
> > and I can't figure it out!!
>
> Didn't we go through multi-table updates the other day?
> I even gave you the link to the page in the documentation that mentions it:
> http://dev.mysql.com/doc/refman/5.0/en/update.html
> Did you read that page?
>
> > UPDATE student_db.student
> > SET has_letter1 = (
> > SELECT i.letter1 FROM olddb.student i, student_db student s WHERE
> > s.unique_key = i.unique_key
> > )
>
> UPDATE student_db.student as news, olddb.student as olds
> SET news.has_letter1 = olds.letter1
> WHERE news.unique_key = olds.unique_key
>

NO actually it's
UPDATE student_db.student as new_student, olddb.student as old_student
SET
new_student.has_completion_reference_letter1 = (
SELECT i.has_ref_letter_1
FROM olddb.student i, student_db.student s
WHERE s.unique_key = i.unique_key
)

produces that "You can't specify.." error still...

> > This query produces the following error:
> >
> > [Quote]You can't specify target table 'student' for update in FROM
> > clause[/Quote]
>
> MySQL has a limitation that you can't read from and write to a given table
> in the same query.
>
> > I can't figure this one out and am required to do so rather urgently
> > (as in before COB today)
>
> Project deadlines are your manager's responsibility. A manager's
> responsibility is to make sure the workers have what they need to finish the
> job -- including time. If he/she hasn't given enough time to accomplish the
> task, it's not _your_ toes that should be on fire.
>
> I am genuinely alarmed that you are doing these changes without any
> preperatory test runs *or* validation tests. I guess that's the government
> for you. :-(
>

And it doesn't help that I have no idea what you're talking about.
Validation tests, preperatory test runs = HUH?

Phil

> Regards,
> Bill K.

Re: Please Help - urgent request Pt II

wrote in message
news:1138646433.857651.120690@g47g2000cwa.googlegroups.com.. .
>> I am genuinely alarmed that you are doing these changes without any
>> preperatory test runs *or* validation tests. I guess that's the
>> government
>> for you. :-(
>
> And it doesn't help that I have no idea what you're talking about.
> Validation tests, preperatory test runs = HUH?

I find that for me, trying to learn a new feature simultaneously with
focusing on the task of finishing real project tasks never works. I can
either concentrate on learning the new feature, or I can concentrate on what
are the correct changes I need to make to the data for my project. So I
need to do a few experiments using scrap data (maybe a copy of my live
database), until I understand the feature. That's what I mean by
preparatory testing. When I'm comfortable with using the feature, I can
move back to the live database and do the changes I need to do.

By validation testing I mean after you make changes to the live database,
making sure that the results of your changes are correct. Do some SELECT
queries against the final, updated database to make sure it looks right;
easy tests are if the field is full of NULLs or all the same value, stuff
like that. The problem is that if you're still struggling with syntax to do
the necessary multi-table updates, you're not going to have time to do these
kinds of tests by COB today.

One more alternative: prior to multi-table updates being implemented in
MySQL, I used another technique to do correlated updates. Run a SELECT on a
join between the two tables you need to correlate, and the output of the
SELECT includes enough static text to form UPDATE statements.

For example:

SELECT CONCAT(
'UPDATE student_db.student SET has_letter1 = ', olds.letter1
'WHERE unique_key = ', news.unique_key, ';' )
FROM student_db.student as news, olddb.student as olds
WHERE news.unique_key = olds.unique_key

Then you can capture the output and run it as a SQL script.

Good luck,
Bill K.

Re: Please Help - urgent request Pt II

I think I know what you mean. We're using a development platform right
now with dummy data in a mirrored database structure to what will be
the live server, live db and live data. That is my development
"sandbox" to get things to work.

Problem is, I'm under an unchangeable deadline of 5 days to get
everything working, and I was never budgeted to learn anything; I'm
expected to just know it (it's the government, as I've said before!)

I'll have to study your CONCAT method a bit more before I try it, as,
once again, I don't understand it.

I've literally have had 3 other queries today (and 1 simple PHP
function) written by 2 other DBAs I found online.

Phil

Bill Karwin wrote:
> wrote in message
> news:1138646433.857651.120690@g47g2000cwa.googlegroups.com.. .
> >> I am genuinely alarmed that you are doing these changes without any
> >> preperatory test runs *or* validation tests. I guess that's the
> >> government
> >> for you. :-(
> >
> > And it doesn't help that I have no idea what you're talking about.
> > Validation tests, preperatory test runs = HUH?
>
> I find that for me, trying to learn a new feature simultaneously with
> focusing on the task of finishing real project tasks never works. I can
> either concentrate on learning the new feature, or I can concentrate on what
> are the correct changes I need to make to the data for my project. So I
> need to do a few experiments using scrap data (maybe a copy of my live
> database), until I understand the feature. That's what I mean by
> preparatory testing. When I'm comfortable with using the feature, I can
> move back to the live database and do the changes I need to do.
>
> By validation testing I mean after you make changes to the live database,
> making sure that the results of your changes are correct. Do some SELECT
> queries against the final, updated database to make sure it looks right;
> easy tests are if the field is full of NULLs or all the same value, stuff
> like that. The problem is that if you're still struggling with syntax to do
> the necessary multi-table updates, you're not going to have time to do these
> kinds of tests by COB today.
>
> One more alternative: prior to multi-table updates being implemented in
> MySQL, I used another technique to do correlated updates. Run a SELECT on a
> join between the two tables you need to correlate, and the output of the
> SELECT includes enough static text to form UPDATE statements.
>
> For example:
>
> SELECT CONCAT(
> 'UPDATE student_db.student SET has_letter1 = ', olds.letter1
> 'WHERE unique_key = ', news.unique_key, ';' )
> FROM student_db.student as news, olddb.student as olds
> WHERE news.unique_key = olds.unique_key
>
> Then you can capture the output and run it as a SQL script.
>
> Good luck,
> Bill K.

Re: Please Help - urgent request Pt II

Hi Phillip,

Is this a homework assignment that you're supposed to be figuring out?
Just curious.

Don


On 30 Jan 2006 09:37:48 -0800, "phillip.s.powell@gmail.com"
wrote:

>Now I have another SQL query for MySQL I can't figure out!! This is
>overwhelming me completely and I also must have this figured out today
>and I can't figure it out!!
>
>UPDATE student_db.student
> SET has_letter1 = (
> SELECT i.letter1 FROM olddb.student i, student_db student s WHERE
>s.unique_key = i.unique_key
> )
>
>This query produces the following error:
>
>[Quote]You can't specify target table 'student' for update in FROM
>clause[/Quote]
>
>I can't figure this one out and am required to do so rather urgently
>(as in before COB today)
>
>Thanx
>Phil

Re: Please Help - urgent request Pt II

It's work. And we have no DBA here for him/her to do this for me (I'm
a web guy)

Phil

Don wrote:
> Hi Phillip,
>
> Is this a homework assignment that you're supposed to be figuring out?
> Just curious.
>
> Don
>
>
> On 30 Jan 2006 09:37:48 -0800, "phillip.s.powell@gmail.com"
> wrote:
>
> >Now I have another SQL query for MySQL I can't figure out!! This is
> >overwhelming me completely and I also must have this figured out today
> >and I can't figure it out!!
> >
> >UPDATE student_db.student
> > SET has_letter1 = (
> > SELECT i.letter1 FROM olddb.student i, student_db student s WHERE
> >s.unique_key = i.unique_key
> > )
> >
> >This query produces the following error:
> >
> >[Quote]You can't specify target table 'student' for update in FROM
> >clause[/Quote]
> >
> >I can't figure this one out and am required to do so rather urgently
> >(as in before COB today)
> >
> >Thanx
> >Phil

Re: Please Help - urgent request Pt II

It's resolved, thanx to another DBA that I know that had to, once
again, write that one for me:

update student_db.student s
set s.activities =
select i.activities
from olddb.applicant i
where s.unique_key = i.unique_key

AND

update student_db.student s
set s.completion_letter1 =
select ica.completion_letter1
from olddb.application_completion ica, olddb.application i
where ica.applicant_id = i.id
and s.unique_key = i.unique_key

There you have it, both of them done.. by someone else :(

Phil

Bill Karwin wrote:
> wrote in message
> news:1138646433.857651.120690@g47g2000cwa.googlegroups.com.. .
> >> I am genuinely alarmed that you are doing these changes without any
> >> preperatory test runs *or* validation tests. I guess that's the
> >> government
> >> for you. :-(
> >
> > And it doesn't help that I have no idea what you're talking about.
> > Validation tests, preperatory test runs = HUH?
>
> I find that for me, trying to learn a new feature simultaneously with
> focusing on the task of finishing real project tasks never works. I can
> either concentrate on learning the new feature, or I can concentrate on what
> are the correct changes I need to make to the data for my project. So I
> need to do a few experiments using scrap data (maybe a copy of my live
> database), until I understand the feature. That's what I mean by
> preparatory testing. When I'm comfortable with using the feature, I can
> move back to the live database and do the changes I need to do.
>
> By validation testing I mean after you make changes to the live database,
> making sure that the results of your changes are correct. Do some SELECT
> queries against the final, updated database to make sure it looks right;
> easy tests are if the field is full of NULLs or all the same value, stuff
> like that. The problem is that if you're still struggling with syntax to do
> the necessary multi-table updates, you're not going to have time to do these
> kinds of tests by COB today.
>
> One more alternative: prior to multi-table updates being implemented in
> MySQL, I used another technique to do correlated updates. Run a SELECT on a
> join between the two tables you need to correlate, and the output of the
> SELECT includes enough static text to form UPDATE statements.
>
> For example:
>
> SELECT CONCAT(
> 'UPDATE student_db.student SET has_letter1 = ', olds.letter1
> 'WHERE unique_key = ', news.unique_key, ';' )
> FROM student_db.student as news, olddb.student as olds
> WHERE news.unique_key = olds.unique_key
>
> Then you can capture the output and run it as a SQL script.
>
> Good luck,
> Bill K.