Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Re: inserting a column into mySQL DB with perl

by Skeeve (Parson)
on Jan 20, 2016 at 21:51 UTC ( [id://1153208]=note: print w/replies, xml ) Need Help??


in reply to inserting a column into mySQL DB with perl

So did I get it right? You have both shown tables in you DB and you have a third table in a text file containing an idCars combined with an idSize, so something like this: 

idCars   idSize
------   ------
1        3
1        2
2        1

[download]

And you want to get: 

Car          Size
----------   ----------
MiniCooper   sedan
MiniCooper   compact
Civic        subcompact

[download]

I'd create a new table CarSize with the content of your text.

The query (your vlookup) would then be something like this: 

select Car, Size
from CarSize
join Cars
using(idCars)
join Size
using(idSize)

[download]

Note: Untested SQL Code I'd write for Oracle. The join-syntax might vary on mySQL.

s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
+.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e

Replies are listed 'Best First'.
Re^2: inserting a column into mySQL DB with perl
by diyaz (Beadle) on Jan 20, 2016 at 22:15 UTC
    That is correct, I have third table in tab-delim file like that. I am assuming I should be normalizing the "Size" of the cars, which is why I have a separate table coding for size of cars. So technically I am trying to update the table, "Cars" to this: 
    idCars  Car         Size
------  ---         ----
1       MiniCooper  1
2       Civic       2
3       Camry       3
4       Accord      3
    Size is the foreign key to idSize in table, "Size". I'm assuming this is the correct way to normalize? I see so similar to what NetWallah is saying, this would make a relationship table and it is unnecessary to have table, "Cars" with the extra column, "Size"? With the query you provided for me above, it essentially is selecting two columns, Car and Size from empty table CarSize. Then it is filling column, Car with idCars and Size with idSize. However, I am confused from what tables it would be pulling idCars and idSize? From your syntax it would be "from" CarSize, which I thought would be empty?
[reply]

In Section Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://1153208]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others wandering the Monastery: (6)
As of 2024-04-23 15:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found