Help With Select Statement 
Author Message
 Help With Select Statement

Can anybody tell me why the following doesn't work?

INSERT INTO newtable
SELECT * FROM oldtable
WHERE oldtable.id = referencetable.id

As you can see, I have 3 tables and try to check the value of [id] field
against a value in another table, then move the record to a new table. I
have no idea what is wrong with the syntax!  What's more, even this doesn't
work:

INSERT INTO newtable
SELECT * FROM oldtable
WHERE mid(oldtable.id,4,1) not between 'A' and 'z'



Sat, 03 Feb 2001 03:00:00 GMT  
 Help With Select Statement

Quote:
>Can anybody tell me why the following doesn't work?

>INSERT INTO newtable
>SELECT * FROM oldtable
>WHERE oldtable.id = referencetable.id

>As you can see, I have 3 tables and try to check the value of [id] field
>against a value in another table, then move the record to a new table. I
>have no idea what is wrong with the syntax!  What's more, even this doesn't
>work:

>INSERT INTO newtable
>SELECT * FROM oldtable
>WHERE mid(oldtable.id,4,1) not between 'A' and 'z'

The most probable reason is than the number of columns returned by SELECT does not coinside with
that in the table you are inserting into. Also, check the datatypes to make sure that implicit
conversion (if necessary) can be performed by the database engine you are writing for.


Sat, 03 Feb 2001 03:00:00 GMT  
 Help With Select Statement
Gene,
You need to add the "referencetable" to the "FROM" clause inorder for it to
be included in the query. So for instance.

INSERT INTO newtable
SELECT * FROM oldtable, referencetable
WHERE oldtable.id = referencetable.id

Hope this helps.

Oscar Bowyer

Quote:

>Can anybody tell me why the following doesn't work?

>INSERT INTO newtable
>SELECT * FROM oldtable
>WHERE oldtable.id = referencetable.id

>As you can see, I have 3 tables and try to check the value of [id] field
>against a value in another table, then move the record to a new table. I
>have no idea what is wrong with the syntax!  What's more, even this doesn't
>work:

>INSERT INTO newtable
>SELECT * FROM oldtable
>WHERE mid(oldtable.id,4,1) not between 'A' and 'z'



Sat, 03 Feb 2001 03:00:00 GMT  
 Help With Select Statement
Quote:

> Gene,
> You need to add the "referencetable" to the "FROM" clause inorder for it to
> be included in the query. So for instance.

> INSERT INTO newtable
> SELECT * FROM oldtable, referencetable
> WHERE oldtable.id = referencetable.id

I think you will need oldtable.* or you'll get all the fields in each
table - otherwise okay.
Of course, the structure of newtable and oldtable would need to be the
same for this to work.

PK



Sun, 04 Feb 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Newbie seeks help with Select Statement

2. help with select statement

3. Help with Select statement in SQL

4. Help with Select statement in SQL

5. Need help with Select statement syntax to retrieve Access2000 record between two dates

6. Help with SELECT statement

7. Need help with Select statement syntax to retrieve Access2000 record between two dates

8. Help with SELECT statement

9. Help with select statement

10. Please Help: Long select statement for ADO

11. Help with Select statement and 2 variables

12. How to select the current record only with SELECT statement

 

 
Powered by phpBB® Forum Software