Insert in related tables

Question

Insert in related tables

Navigation

#1 by (1 votes)

1

That's right, I'm not sure how to do this.

tb_Customer(id_Customer INTEGER [PK], nm_Customer VARCHAR, cpf_cnpj NUMERIC)

dm_address_type(cd_address_type CHAR(1) [PK], ds_address_type VARCHAR)

tb_customer_address(id_customer [PFK],  cd_address_type CHAR(1)[PFK], street VARCHAR, lot INTEGEER, references VARCHAR, zip_code VARCHAR)

SELECT * FROM dm_address_type
cd_address_type: R , C , O
ds_address_type: Residencial, Comercial, Outros

My question is as follows, to register the client 'JOIN' would the following SQL?:

Joãozinho Silva - 888.777.666-55

Residential Address: Rua das Flores, 1. Zip Code: 01234-567 Business Address: Rua das Pedras, 100 Conjunto 200. Zip / Postal Code: 01234-567

INSERT INTO tb_Customer(nm_customer, cpf_cnpj) VALUES ("Jãozinho Silva", "888.777.666-55")

SET @ic_Customer = LAST_INSERT_ID()

INSERT INTO tb_customer_address(street, lot, references, zip_code, id_customer, cd_address_type) values ('Rua das Flores', '1', 'references', 01234-567', @id_Customer, 'R')

INSERT INTO tb_customer_address(street, lot, references, zip_code, id_customer, cd_address_type) values ('Rua das Pedras', '100 Conjunt 200', 'references', 01234-567', @id_Customer, 'C')

And another question is how many different addresses can I register for each customer? How many needed, right?

Vlww staff. Hugs!

mysql sql relacionamento sql-insert

asked by anonymous 13.05.2018 / 16:30

1 answer

How to sort field of type VARCHAR in CodeIgniter Append .html extension to the end of all links in multiple files (with Shell Script)

score 1 · Accepted Answer

By your choice of TAG it was difficult to know if you are using SQL Server or MySQL.

Therefore:

If it's SQL Server: You can not use LAST_INSERT_ID, you need to get the data otherwise. You can do this by using: SCOPE_IDENTITY (), @@ IDENTITY, or IDENT_CURRENT. You can read more about these 3 options this link .
If it's MySQL Server: I created a bank here according to what you said you put in yours. Still some things will depend on how you created your bank, but:

1 - When you attempt to execute this INSERT you will receive a truncated data error because the CPF contains points. I can not tell you if there are ways to format the CPF to insert into the table, but you can do this later in the query and in the program that will use the data, so there is no need to write the data with the points like you did; / p>

2 - You have defined the variable as @ic_Customer, but on the lines below you define @id_Customer;

3 - The word 'references' is a reserved MySQL word. It is not recommended to use reserved words in your database;

4 - In the second address, in '100 Conjunt 200' you will have problems with truncated data. You have defined this field as an INTEGER. Taking this sample text you have a 15B size field. Integers support up to 8B (when you use the BigInt option.) I would separate the house number and the plugin into two different fields (one for number with numerical value and another for complement with varchar);

5 - There are some symbol errors there, the correct one would be that your code would be as follows:

INSERT INTO tb_Customer(nm_customer, cpf_cnpj) VALUES ("Jãozinho Silva", "88877766655");

SET @id_Customer = LAST_INSERT_ID();

INSERT INTO tb_customer_address(street, lot, reference, zip_code, id_customer, cd_address_type) values ('Rua das Flores', '1', 'references', '01234-567', @id_Customer, 'R');

INSERT INTO tb_customer_address(street, lot, reference, zip_code, id_customer, cd_address_type) values ('Rua das Pedras', '100 Conjunt 200', 'references', 01234-567', @id_Customer, 'C')

6 - Yes, you can register multiple addresses for the same client.