Gossamer Forum
Home : Products : Gossamer Links : Discussions :

SQL Syntax Question

Quote Reply
SQL Syntax Question
Hi:

Thought I would ask here before I run this and ruin my data!

I want to take the CategoryID from CatLinks and copy that into the corresponding Link field CategoryNum (that I have added.) Does this look right?
In Reply To:
insert into Links (CategoryNum)
Select LinkID,CategoryID from CatLinks
where Links.ID = 'LinkID'
I am sure I do not quite have this right!

thanks!

Dave

Quote Reply
Re: SQL Syntax Question In reply to
insert into Links (CategoryNum)
Select LinkID,CategoryID from CatLinks
where Links.ID = 'LinkID'


This is sort of a stripped down version of what I am trying to do.
But, your query may actually work:

insert into Links (CategoryNum)
Select CategoryID from CatLinks,Links
where Links.ID = CatLinks.LinkID

That _seems_ to do it. But no guarantees.

PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://pugdog.com/FAQ


Quote Reply
Re: SQL Syntax Question In reply to
Pugdog:

Thanks- I'll give it a whirl! At this point, as long as it only effec ts that one field, I am ok!

dave

Quote Reply
Re: SQL Syntax Question In reply to
Pugdog:

Tried your suggestions, no go. Got this error: Unknown table 'new_Links' in where clause

Then I had a different idea, and approached it this way:

In Reply To:
update new_Links
set CategoryNum = 'new_CatLinks.CategoryID'
where 'new_CatLinks.LinkID' = 'new_Links.ID'
(Obviously, I am using "new_" as a prefix on tables...)

It seemed right... in fact, this one ran, but "Rows affected: 0"... (I am change that to Rows Infected: 0 someday...)

back to the drawing board :}

dave

Quote Reply
Re: SQL Syntax Question In reply to
Check your database... that would imply there is no table called 'Links' in your database, or rather no table called 'new_Links' in your database.

If you have a "Links" table, rename it to "new_Links" and rename the .def file as well.

PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://pugdog.com/FAQ


Quote Reply
Re: SQL Syntax Question In reply to
Pugdog:

That is what I read it to mean, too. But there is a table called new_Links, and there is a def file new_Links.def.

I built the tables normal, with the prefix "new_" to keep my original table data... so all the tables should have the right names with the "new_"

here is exactly what I am trying to run:

insert into new_Links (CategoryNum)
Select LinkID,CategoryID from new_CatLinks
where new_Links.ID = 'LinkID'

which seems right! But I just did it again, and got this:

Error: Query Error: Failed to execute query: 'insert into new_Links (CategoryNum) Select LinkID,CategoryID from new_CatLinks where new_Links.ID = 'LinkID'' Reason: Unknown table 'new_Links' in where clause

Was there an episode of Twilight Zone about SQL???

dave

Quote Reply
Re: SQL Syntax Question In reply to
Are you executing the query with the right default database? Maybe it's looking in a different database?

PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://pugdog.com/FAQ


Quote Reply
Re: SQL Syntax Question In reply to
Hmmmmm...

I am just running through the SQL Momitor in admin. I am running lots of other queries today, simple "Update" and "Select" things, and they work great. Getting the results I expect, andf the changes appear online.

I will play with the syntax more today and see what I can find out...

dave