Jump to content


Photo

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


  • Please log in to reply
1 reply to this topic

#1 sgalmeida

sgalmeida
  • Members
  • Pip
  • Newbie
  • 3 posts

Posted 17 December 2003 - 12:10 PM

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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 17 December 2003 - 07:28 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users