a quick and simple(?) question about SQL commands

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

a quick and simple(?) question about SQL commands

Author

Topic

will500
Starting Member

7 Posts

Posted - 2008-02-28 : 15:40:19

I think I have a rather easy one but I'm somewhat new to SQL.

I am currently copying 30,000 of my forums users over to my auction software.

I want to copy some fields from the my "users" table to some fields in the my auction table. Which are all located on the same database.

I've been reading: http://dev.mysql.com/doc/refman/4.1/en/insert-select.html
to help me learn this.

However, I am a bit lost. Could someone give me an example of how to select a table and field, then copy that field into another table/field?

I mean a command to run? Then I can go from there. :-)

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-28 : 15:46:01

INSERT INTO Table2 (Column1, Column2)
SELECT Column1, Column2
FROM Table1
WHERE ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

will500
Starting Member

7 Posts

Posted - 2008-02-28 : 16:06:37

wow, thanks for such a quick response!

Do I need the "WHERE" command?

Oops, also, I need to put these in a certain line in the field, starting at line 100, as I already have 100 users. As in, I am importing 30,000 users into an existing field that already has 100 users, how do I specify "start place at line 101".

Thanks again!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-28 : 16:34:44

You can do something like this to get the rows that don't exist in the destination table:

INSERT INTO Table2 (Column1, Column2)
SELECT Column1, Column2
FROM Table1 t1
WHERE NOT EXISTS (SELECT * FROM Table2 t2 WHERE t1.Column1 = t2.Column1)

Column1 would most likely be your userid column or your primary key column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

will500
Starting Member

7 Posts

Posted - 2008-02-28 : 16:46:10

Thanks, you are being a life saver here.

I'm a little confused as to what "FROM Table1 t1" what is t1?

table 1 = the table/columns we are taking info from. table2 = putting info to, I got that.

I like your "WHERE NOT EXISTS" but isn't there some sort of " > 101" command after the line which means "start at 101" or something?

I just want to get this 100% right before I run this script and have to restore a backup or something, or is there such a thing as an "undo" command? heh.

Excuse my noobishness, we all have to start somewhere, but this will be a very valuable skill! Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-28 : 16:53:02

Run the query without the INSERT line in there to see exactly which rows it will be inserting.

t1 and t2 are just aliases so that I don't have to type in the table names again.

Since I don't know what your tables look like, the easiest way to do this was to use NOT EXISTS. This query will grab everything from the source table that does not already exist in the destination table. If you have some column that specifies the "line number", then we could use that instead. Either way would work, but we would need to see your table layout in order to help further.

Put the query in a transaction if you want to be able to "undo" it if anything goes wrong. Hint: BEGIN TRAN, COMMIT/ROLLBACK TRAN.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

will500
Starting Member

7 Posts

Posted - 2008-02-28 : 17:24:31

Haha, I'm getting an error, but I think we are close!

INSERT INTO probid_users.id
SELECT probid_users.id
FROM user.userid
WHERE NOT EXISTS (SELECT * FROM probid_users WHERE user.userid = probid_users.id)

probid_users = user table moving to
id = column moving to
users = user table moving from
userid = column moving from

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 17:30:48

[code]INSERT probid_users
(
id
)
SELECT u.userid
FROM users AS u
WHERE NOT EXISTS (SELECT * FROM probid_users AS x WHERE x.id = u.userid)[/code]

E 12°55'05.25"
N 56°04'39.16"

will500
Starting Member

7 Posts

Posted - 2008-02-28 : 17:53:03

Thank you I get this error:

EXISTS (SELECT * FROM probid_users AS 1 WHERE 105.id = u.userid

I'm using mysql4

and the command I'm using is:

INSERT probid_users
(
id
)
SELECT u.userid
FROM users AS u
WHERE NOT EXISTS (SELECT * FROM probid_users AS 1 WHERE 105.id = u.userid)

maybe I didn't know what x meant....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 17:55:34

MySQL don't accept x as table alias?

INSERT	probid_users
	(
		id
	)
SELECT	u.userid
FROM	users AS u
WHERE	NOT EXISTS (SELECT * FROM probid_users AS pu WHERE pu.id = u.userid)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 17:56:19

[code]INSERT probid_users
(
id
)
SELECT u.userid
FROM users AS u
LEFT JOIN probid_users AS pu ON pu.id = u.userid
WHERE pu.id IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-28 : 18:15:48

quote:
Originally posted by will500

I'm using mysql4

You are posting your question on a Microsoft SQL Server site, so our answers will be for T-SQL. You'd have better luck on a mysql forum such as the one over at dbforums.com.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

will500
Starting Member

7 Posts

Posted - 2008-02-28 : 18:19:41

doh! Sorry. Anyway:

Inserted rows: 29866 (Query took 0.5625 sec)
SQL query:
INSERT probid_users(
id
)
SELECT u.userid
FROM user AS u
LEFT JOIN probid_users AS pu ON pu.id = u.userid
WHERE pu.id IS NULL

It worked!!!!! Thanks!

Peso, since you seem to be familiar with mysql, if I wish to chance an entire fields data to one variable, what command would that be?

As in I'm giving all of these users a $3.00 credit on their accounts so I want their account abalance, all of them, to show as -3.00

and big thanks to tkuzer too! For future questions I will go to dbforums.com, thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-28 : 18:22:16

UPDATE Table1
SET SomeColumn = -3

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

will500
Starting Member

7 Posts

Posted - 2008-02-28 : 20:49:00

Thank you for all of the help, I will be continuing this in this thread, on the dbforum site.

http://www.dbforums.com/showthread.php?t=1627863

You guys are life (well... money...) savers! :-)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-29 : 05:40:01

<<
MySQL don't accept x as table alias?
>>

It does.
But OP used 1 as alias name which is wrong

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-29 : 06:03:38

I see now.
He both changed the alias to "1" and then select from alias "105", which I suggested neither.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources