Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Insert Data from one Table to Another with WHERE conditions

I have two tables

Table A has two columns I need to use and Table B has numerous columns I need to Populate with common information

INSERT INTO gs_object_sensors(imei) SELECT imei FROM gs_user_objects WHERE user_id = '14'

The above code works and inserts the IMEI number based on the WHERE clause. What I need is to incorporate the above into the following INSERT command

INSERT INTO table_name (imei, name, description, ...) VALUES (*IMEI*, Disconnect, Battery Disconnect, ...);

On the above sql statement I need the IMEI from the first SQL statement with the additional name and description to be added to Table B. The imei is the row information from Table A and the user_id is also from Table A just being used for the WHERE clause. Name and Description will be a constant not found in Table A

INSERT INTO gs_object_sensors(imei) SELECT imei FROM gs_user_objects WHERE user_id = '14'

I used this code and inserts the required information from Table A but need to add additional constant information to Table B

Tags:

2

Answers


  1. 0

    example QUERY to resolve the issue:

    INSERT INTO gs_object_sensors (imei, name, description, status)
SELECT imei, 'Disconnect', 'Battery Disconnect', 'Inactive' 
FROM gs_user_objects 
WHERE user_id = '14';

    where the data in quotes 'Disconnect', 'Battery Disconnect', 'Inactive' are additional constants that can be inserted to the Table B.

    Login or Signup to reply.
  2. 0

    Steps:

    1. List All Columns: Identify and list all the columns you need to
      insert data into in table_name.
    2. Select Corresponding Data: Use the SELECT statement to fetch the
      IMEI values and include constant values for columns that are not
      coming from Table A.
    3. Match Column Order: Ensure the order of columns in INSERT INTO matches the
      order of data returned by the SELECT statement.
    INSERT INTO table_name (imei, name, description, additional_column1, additional_column2)
SELECT imei, 'Disconnect', 'Battery Disconnect', additional_value1, additional_value2
FROM gs_user_objects
WHERE user_id = '14';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search