By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,992 Members | 967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,992 IT Pros & Developers. It's quick & easy.

Please Help - urgent request Pt II

P: n/a
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:

You can't specify target table 'student' for update in FROM
clause
I can't figure this one out and am required to do so rather urgently
(as in before COB today)

Thanx
Phil

Jan 30 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
<ph**************@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.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:

You can't specify target table 'student' for update in FROM
clause

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.
Jan 30 '06 #2

P: n/a
See below, thanx

Bill Karwin wrote:
<ph**************@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.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:

You can't specify target table 'student' for update in FROM
clause


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.


Jan 30 '06 #3

P: n/a
<ph**************@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.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.
Jan 30 '06 #4

P: n/a
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:
<ph**************@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.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.


Jan 30 '06 #5

P: n/a
Don

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, "ph**************@gmail.com"
<ph**************@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:

You can't specify target table 'student' for update in FROM
clause

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

Thanx
Phil

Jan 31 '06 #6

P: n/a
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, "ph**************@gmail.com"
<ph**************@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:

You can't specify target table 'student' for update in FROM
clause

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

Thanx
Phil


Jan 31 '06 #7

P: n/a
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:
<ph**************@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.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.


Jan 31 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.