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.stud ent
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 7 1481
<ph************ **@gmail.com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.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.stud ent SET has_letter1 = ( SELECT i.letter1 FROM olddb.student i, student_db student s WHERE s.unique_key = i.unique_key )
UPDATE student_db.stud ent as news, olddb.student as olds
SET news.has_letter 1 = 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.
See below, thanx
Bill Karwin wrote: <ph************ **@gmail.com> wrote in message news:11******** **************@ g43g2000cwa.goo glegroups.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.stud ent SET has_letter1 = ( SELECT i.letter1 FROM olddb.student i, student_db student s WHERE s.unique_key = i.unique_key )
UPDATE student_db.stud ent as news, olddb.student as olds SET news.has_letter 1 = olds.letter1 WHERE news.unique_key = olds.unique_key
NO actually it's
UPDATE student_db.stud ent as new_student, olddb.student as old_student
SET
new_student.has _completion_ref erence_letter1 = (
SELECT i.has_ref_lette r_1
FROM olddb.student i, student_db.stud ent 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.
<ph************ **@gmail.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.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.stud ent SET has_letter1 = ', olds.letter1
'WHERE unique_key = ', news.unique_key , ';' )
FROM student_db.stud ent 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.
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.goo glegroups.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.stud ent SET has_letter1 = ', olds.letter1 'WHERE unique_key = ', news.unique_key , ';' ) FROM student_db.stud ent 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.
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.stud ent 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
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.stud ent 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
It's resolved, thanx to another DBA that I know that had to, once
again, write that one for me:
update student_db.stud ent s
set s.activities =
select i.activities
from olddb.applicant i
where s.unique_key = i.unique_key
AND
update student_db.stud ent s
set s.completion_le tter1 =
select ica.completion_ letter1
from olddb.applicati on_completion ica, olddb.applicati on i
where ica.applicant_i d = 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.goo glegroups.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.stud ent SET has_letter1 = ', olds.letter1 'WHERE unique_key = ', news.unique_key , ';' ) FROM student_db.stud ent 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Joao Santa Barbara |
last post by:
Hi all
i have this error reading one registry key , can anyone assist me .. please
very urgent
i try in one computer, and work fine, but in other computer it didn´t work
and throws this error ..???
"Request for the permission of type
|
by: sathya |
last post by:
hi,
i have problem in httphandler, my problem is that when i am trying to
use server.execute(/default.aspx) i am getting error....
Here i am trying to redirect from home.aspx to default.aspx (both file
isin sharepoint).I have give a copy of my code below..
|
by: VS_NET_DEV |
last post by:
Hi all,
I am running asp.net on IIS 6.0 /IE 6.0 on Windows 2003 server.
I have form authenication and in Global.asax I have:
private void Global_AcquireRequestState(object sender,
System.EventArgs e)
{
if (Session == null)
{
if (!Request.Path.ToUpper().EndsWith("LOGIN.ASPX"))
|
by: sathya |
last post by:
hi,
i have problem in httphandler, my problem is that when i am trying to
use server.execute(/default.aspx) i am getting error....
Here i am trying to redirect from home.aspx to default.aspx (both file
isin sharepoint).I have give a copy of my code below..
|
by: |
last post by:
Hi all, I have a website running on beta 2.0 on server 2003 web sp1 and I
keep getting the following error:-
Error In:
http://www.mywebsite.org/WebResource.axd?d=5WvLfhnJp5Lc8WhQSD4gdA2&t=632614619884218750
--------------------------------------------------------------------------------
System.Security.Cryptography.CryptographicException: Padding is invalid and
cannot be removed. at...
| |
by: SKJoy2001 |
last post by:
PLEASE HELP ME!!! P E R L!!!
I have a CGI (PERL) file namely 'test.cgi' and it has the correct permission (755) on the FTP server and it is within the CGI path.
I have the following code in it:
===================================
1: #!/usr/bin/perl
2:
3: $cr = '???';
|
by: John Austin |
last post by:
I need to understand why if I add a control and use AddHandler to connect its
click event, it will work in Page_Load, but not in a Button_Click.
The idea is that the user types some data, presses the button, gets a list
of results (each with a LinkButton) and can then press one of the link
buttons to get further information. The newly added link buttons appear, but
the click event added with AddHandler does not fire.
A control added in...
|
by: |
last post by:
I'm looking for some design guidance on a collection of projects I'm working
on.
The project involves a bunch of websites constructed out of a collection of
user controls. Different user populations with different access rights and
"roles" will be visiting the site. I will be using ASP.NET 2.0's membership,
roles, and profiles stuff to manage access.
User controls need to be visible or not visible depending on user role. In
some...
|
by: Nehad9 |
last post by:
Hi,
I am moving an existing ASP application from Win 2k3, IIS 6.0 to a new Win2k3 IIS 6.0 but the same pice of code that works on the old machine give a "blank" page on new machine. On debugging, I found out that none of the request values are being passed. The whole application works on request values and changing the code would not be possible.
any help would be highly appreciated since this is an urgent request.
Thanks
Neha
|
by: jats |
last post by:
I am a .NET programmer and i had recently got a web application designed in Classic ASP, to make some modifications.
When i run the application and try to create a new user the following error pops up. "The email address you entered already exists."
Now my problem is i am not able to find out the where the error is generating or why as the datdabase table is completely empty (no records)
Below is the code...
++++++++++++++++++
<%
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |