SQL Insert Statement not working

Question

1.00/5 (1 vote)

See more:

I have two tables and I needed to get a data column from one table to another. I have created a column named LName in one table. In the other table the column LName has data in it and my code just won't work. I am trying to get the data to insert based on User ID. What am I doing wrong?

SQL

INSERT INTO Table20 (LName)

SELECT LName FROM Table21

WHERE LName = User_ID

Table20 Layout

HTML

User_ID       LName         Color           Car
10001                        Red            Ford
10002                        Blue           Ford

Table21 Layout

HTML

User_ID       LName        
10001         Mike
10002         James

Table20 End Result

HTML

User_ID       LName         Color           Car
10001         Mike           Red            Ford
10002         James          Blue           Ford

Posted 23-Jan-15 7:00am

Computer Wiz99

Add a Solution

Comments

Wendelius 23-Jan-15 13:03pm

What error do you get?

Computer Wiz99 23-Jan-15 13:05pm

I don't get an error. It inserts the data but puts it a new column at the bottom of the table. It adds more records to the table and not inserting it in the right place.

ZurdoDev 23-Jan-15 13:21pm

Insert will add records. You need to try and explain your problem better.

Computer Wiz99 23-Jan-15 13:22pm

I used the code you gave and I Executed it and it said that 771 row affected. I did a refresh of the table and there is no data in column LName. What did I do wrong this time?

Wendelius 23-Jan-15 13:25pm

You can reply directly to the answer. I'll update it shortly.

ZurdoDev 23-Jan-15 13:10pm

Your sql will insert a record into Table20. Is that what you want?

2 solutions

Solution 2

you need update with join, for example

SQL

UPDATE a
  SET a.LName = b.LName
from Table20  a
  INNER JOIN Table21  b
    ON a.User_ID        = b.User_ID

Posted 23-Jan-15 7:28am

DamithSL

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-01-23T07:05:00

Are you actually trying to update the data, not insert new rows? I mean something like:

SQL

UPDATE Table20
SET LName = (SELECT t21.LName 
             FROM Table21 t21
             WHERE t21.User_ID = Table20.User_ID)

INSERT statement would be used in a situation where you're about to add new rows to the table. UPDATE then again is used for updating data in columns for existing rows.

The statement above uses a correlated subquery to update the LName. This means that each of the rows in Table20 is looped and for each row the statement tries to find corresponding LName using the User_ID as a key. As you can see from the aliases used in the correlated subquery, the User_Id is fetched from the outer statement (Table20) and matched with the inner statement (alias t21).

From OP:
I used the code you gave and I Executed it and it said that 771 row affected. I did a refresh of the table and there is no data in column LName.

Try the following query

SQL

SELECT T20.User_ID, T21.LName, T20.Color, T20.Car
FROM Table20 T20 
     INNER JOIN Table21 T21 ON  T20.User_ID = T21.User_ID;

Does that query show the rows and fetch the LName to the result set correctly? If not then the user id's are not matching between the tables.

SQL Insert Statement not working

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

SQL Insert Statement not working

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us