Insert value in Table Y for all existing Table X Ids

Asked

Viewed 103 times

2

I have a table called CONTAS with a column account_id.

Tabela contas

I have another table called PREMIO with columns account_id, id_premio and itemqtd

Tabela premio

I wanted a command where I inserted the id_premio and itemqtd but for the column called account_id he insert all account_id table CONTAS because I want to give the same prize and quantity to all accounts registered at that time.

mysql sql sql-insert

2 answers

1


This should solve your problem

insert into premio(contas_id, id_premio, qtd_premio)
select contas_id, 01, 1500 from contas

Explaining: Will be made one select in all table data CONTAS bringing the field contas_id, the prize code (01) and prize amount (1500) and the same will be included in the prize table.

Modify the sample values according to your need.

Note: Be careful with the primary keys, if the field id_premio is the primary key of the table PREMIO you will have to see how you will treat properly.

EDITED

The Insert would look like this

insert into entregador(account_id, id_premio, itemqtd)
select account_id, 1, 13 from login

  • Opa thanks, but got the following error: #1054 - Column 'counts_id' unknown in 'field list' the column name is correct and exists in the table, but counts_id is the primary key of the table ACCOUNTS already id_prize is not primary key

  • There is something strange then in the structure that you went through... There is no way you edit your answer and by an image of the structure of each table?

  • I used fictitious names to explain but I’ll pass as is: Table of accounts ( called login ) - http://prntscr.com/ow8jfa Table awards

  • Check the edition of my answer, it is strange that this error quoted by you occurs if the field exists...

  • 1

    Thank you so much man for your attention help, it worked out the way I needed! Ps: the first attempt I typed a wrong character and did not see. rs

0

select CONTAS_ID into PREMIO from CONTAS

When we use INTO we play all results to the informed table.

  • it returns a LIMIT error 0, 25 and on this line where I put ( ID_PREMIO ) and ( QTD_PREMIO ) numeric type 444 and 1

Browser other questions tagged mysql sql sql-insert

You are not signed in. Login or sign up in order to post.