SQL query INSERT SELECT COUNT from one table to another row by row

Question

I have trouble finding the correct SQL Query. As you can see in the picture, I have two Tables ('Table_1' with column 'VEHICULES') and ('Table_2' with column 'IMMATRICULATION' and 'NOMBRE'). In the Table_2 I want to insert in the column ('Nombre') the counted values from the Table_1 of VEHICULE (CarA, CarB and CarC) in the same row of these values as in the picture.

I tried the below query but it is added a value instead of placing it in the same row of CarA, CarB....

INSERT INTO Table_2 (NOMBRE)
    VALUES ((SELECT COUNT(*) FROM Table_1 WHERE VEHICULES = 'CarA'),
            (SELECT COUNT(*) FROM Table_1 WHERE VEHICULES = 'CarB'),
            (SELECT COUNT(*) FROM Table_1 WHERE VEHICULES = 'CarC')
           );

Picture with tables and required result

See meta.stackoverflow.com/questions/333952/…

Strawberry
– Strawberry

2020-10-09 20:25:30 +00:00
Commented Oct 9, 2020 at 20:25 — Strawberry
– Strawberry, Commented Oct 9, 2020 at 20:25

user8484970 · Accepted Answer · 2020-10-09 19:48:05Z

1

First generate the select query that returns the values you want inserted into the new table, then add INSERT INTO in the beginning. The group by clause is also what you're missing.

Here is an example

INSERT INTO Table_2 (Name, Cnt)
select VEHICULES, count(*)
from Table_1
group by VEHICULES

answered Oct 9, 2020 at 19:48

user8484970

Sign up to request clarification or add additional context in comments.

6 Comments

Julie Tarnaud Over a year ago

Like that ? : INSERT INTO Table_2 (NOMBRE) VALUES ((SELECT COUNT () FROM Table_1 WHERE VEHICULES = 'CarA')), ((SELECT COUNT() FROM Table_1 WHERE VEHICULES = 'CarB')), ((SELECT COUNT(*) FROM Table_1 WHERE VEHICULES = 'CarC')); GROUP BY VEHICULES;

user8484970 Over a year ago

No, you don't need to specify a where clause at all. You just need to group by statement and it will count all of the values in that column "VEHICULES". All you need is the SQL example I've listed above and it will automatically insert the correct values for vehicules and count.

Julie Tarnaud Over a year ago

Okay I see ! But is it possible to update it whithout adding each time new values ?

Julie Tarnaud Over a year ago

I mean by keeping CarA, CarB and CarC in the table_2 and changing just the value of column NOMBRE

user8484970 Over a year ago

In that case, use UPDATE INTO instead of INSERT INTO. That will then update those records. Gordons answer below is more complete!

|

Gordon Linoff · Accepted Answer · 2020-10-09 20:07:32Z

0

You seem to want update, not insert:

update table_2 t2 join
       (select vehicules, count(*) as cnt
        from table_1
        group by vehicules
       ) t1
       on t2.immatriculation = t1.vehicules
    set nombre = t1.cnt;

answered Oct 9, 2020 at 20:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Julie Tarnaud Over a year ago

I have this ; Msg 102, Niveau 15, État 1, Ligne 1 Incorrect syntax near 't2'. Msg 102, Niveau 15, État 1, Ligne 5 Incorrect syntax near 't1'.

Gordon Linoff Over a year ago

Your question is tagged MySQL, but that error looks like SQL Server. What database are you really using?

Barbaros Özhan · Accepted Answer · 2020-10-09 21:11:15Z

0

You need update obviously, rather than insert, and if you're using MSSQL Server (which's suggested by the error text you got), then use such a statement :

WITH t AS
(
 SELECT vehicules, COUNT(*) AS cnt
   FROM table_1
  GROUP BY vehicules 
)
UPDATE table_2
   SET nombre = cnt
  FROM t
 WHERE immatriculation = vehicules

Demo

answered Oct 9, 2020 at 21:11

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

SQL query INSERT SELECT COUNT from one table to another row by row

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related