HELP :: script SQL with variables (or sort of...)

[sgalmeida]

Greetings

 

I have a question about SQL on MySQL. I want to make a SQL file to import to MySQL for filling the tables, but I want to preserve the foreign keys.

I want to know if it is possible to use variables (or something like that) in SQL scripts for MySQL. Imagine this: my database has 2 tables: product and lot. The table product is defined as:

 

CREATE TABLE product

(

productCode VARCHAR(32),

price VARCHAR(32),

 

oid INT UNSIGNED AUTO_INCREMENT,

 

PRIMARY KEY(oid)

);

 

and the table lot is defined like:

 

CREATE TABLE lot

(

lotcode VARCHAR(32),

product INT UNSIGNED,

quantity INT,

 

oid INT UNSIGNED AUTO_INCREMENT,

 

FOREIGN KEY(product) REFERENCES product(oid),

 

PRIMARY KEY(oid)

);

 

In this way, while developing the script SQL file, I habe to know the OID\'s of the products when I do the insert on table lot.

 

INSERT INTO product (productCode, price) VALUES (\"Nike001\",\"120\");

INSERT INTO product (productCode, price) VALUES (\"Adidas001\",\"100\");

 

-- were somewere I have to do something like this

-- SELECT oid FROM product WHERE productCode=\"Nike001\" AND price=\"120\";

-- to be able to insert data on lot\'s table.

 

INSERT INTO lot (lotcode, product, quantity) VALUES (\"LotNike001\",oid of product nike001, 12);

 

or

 

INSERT INTO lot (lotcode, product, quantity) VALUES (\"LotNike001\", [sELECT OID FROM product WHERE productCode=\"Nike001\" AND price=\"120\"], 12); <--- does this work??

 

 

So, is it possible to retrieve and save this data (oid of product) and use it in insert on other tables? If not, what you suggest for me to do to achieve what I want (fill the database with data and preserve the foreign keys)

 

Thank you all.

 

Almeida

[Barand]

This should

 

INSERT INTO lot (lotcode, product, quantity)

SELECT \'LotNike001\' as c, OID, 12 as q FROM product WHERE productCode=\'Nike001\' AND price = 120

 

hth