Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Update from Lookup Table

Status
Not open for further replies.

DonP

IS-IT--Management
Jul 20, 2000
684
US
This ought to be simple but I can't seem to get the syntax right:

[tt]Update recordings set Label=(select LabelID from Labels where label=LabelName)[/tt]

I'll try to explain: the table "recordings" has a field "Label" that is currently text, but I am attempting to repopulate it with the numeric "LabelID" from the "labels" table, which has the matching text in its "LabelName" field. In other words, where Label=LabelName, the LabelID needs to replace the value currently in Label.

This is a one time query string and won't need to be run again once it does its job. Can anyone help? Don
don@pc-homepage.com
Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT/2000 (only when I have to!)
 
You're trying to use a sub-select where MySQL currently does not provide one. According to the MySQL online manual (
MySQL Server currently only supports nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT .... You can, however, use the function IN() in other contexts. Subselects are currently being implemented in the 4.1 development tree.

You're going to have to use your programming language to update each value individually. ______________________________________________________________________
TANSTAAFL!
 
or cheat and select everything from both tables into a temporary table, change the field you need and then reload selected parts of the temp table back into the main table.

create temporary table mixnmatch select recodings.*,labels.LabelID from recordings,labels;

update mixnmatch set label = labelid;

dump mixnmatch selected filed (required in recodings) to a textfile.

delete from recordings;

load dump from mixnmatch into recodings.

long winded but quite quick when you do it.

***** BACKING UP DATA IS HIGHLY RECOMMENDED :)
______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.
 
I've done subselects in MySQL before but not in an Update query. In fact, that is how I populated the labels table in the first place. The fact that this is a one-time process means that using a series of operations is no problem and I will do it here on my local copy which is currently in sync with the online one so that the live version will act as a backup. Once "recordings" is updated properly, I can copy its contents to the one online. Thanks for the tips! Don
don@pc-homepage.com
Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT/2000 (only when I have to!)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top