IF INSERT - SQL Server Forums

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

IF INSERT

Author

Topic

madbaho
Starting Member

8 Posts

Posted - 2011-09-27 : 08:33:36

I am using php variable on my insert for SQL.
My table is: videos Columns: video_id(uuid), file_name(charvar), file_path(charvar)
The php variable I want to insert is $value which is for file_name. The query I came up with will not work.

IF EXISTS (SELECT * FROM videos WHERE file_name='$value')
UPDATE videos file_name='$value'
ELSE
INSERT INTO videos VALUES $value"

any suggestions? I am trying to only insert file if it hasn't been inserted.

Much Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-09-27 : 08:47:10

stored procedure perhaps? parameterized query?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 08:48:20

UPDATE videos file_name='$value'

will set EVERY row in the table to have file_name='$value' - to avoid that you need to add:

WHERE video_id = '$SomeIDvariable'

The INSERT needs to be:

INSERT INTO videos (file_name) VALUES ('$value')

although presumably you also need to include file_path? and perhaps also video_id (unless it is automatically assigned)

madbaho
Starting Member

8 Posts

Posted - 2011-09-27 : 09:11:00

the video_id is a UUID... It gets automatically generated when insert is applied. The file_path I have a function that scans directories and sub directories for video files and stores the path name.

I can get a standard query to work by just using standard INSERT with no EXISTS, but with 35k files the method will repeat. Other words I need EXISTS. Only need a query when video doesn't exist.

Thanks for feedback, I am going to experiment with it. My previous post that query wouldn't even run.

Much Thanks! Happy Coding!

madbaho
Starting Member

8 Posts

Posted - 2011-09-27 : 09:15:57

The whole procedure looks like:

$fileLocations = "IF EXISTS (SELECT * FROM videos WHERE file_name='$value')
UPDATE videos file_name='$value'
ELSE
INSERT INTO videos file_name VALUES ('$value')";
queryMysql($fileLocations);

I wrote queryMysql... It does work and when my query runs it throws my custom exception.

Also, Kristen. I tried those changes and the query still throwing my exception.

madbaho
Starting Member

8 Posts

Posted - 2011-09-27 : 09:19:59

UUID will be a ( ' '),$value, $myString

$value is the file_name, $myString is file path and the uui is ' ' ... makes a complete unique ID.

madbaho
Starting Member

8 Posts

Posted - 2011-09-27 : 09:23:17

Here is what I got now...
{
$myString=$dir.$value;
echo "Value: $value<br />\n";

$fileLocations = "IF EXISTS (SELECT * FROM videos WHERE file_name='$value')
UPDATE videos file_name='$value'
ELSE
INSERT INTO videos video_id,file_name,file_path VALUES ('','$value','$myString')";
queryMysql($fileLocations);
}

It is getting closer but still no query. I appreciate the patients!

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 10:42:29

"I wrote queryMysql."

Ah, sorry. This is an MS SQL forum, you may not find people here who know about My SQL - I don't ...

But my points are probably still relevant:

UPDATE videos file_name='$value'

that will update every row in the table to that value.

INSERT INTO videos video_id,file_name,file_path VALUES ('','$value','$myString')"

You need


INSERT INTO videos (video_id,file_name,file_path) VALUES ('','$value','$myString')

as I mentioned earlier.

"the video_id is a UUID... It gets automatically generated when insert is applied"

I don't know what UUID is, but my expeectation is that you should OMIT that column altogether from the INSERT in order that it will be automatically provided, i.e.


INSERT INTO videos (file_name,file_path) VALUES ('$value','$myString')

Note that if video_id is numeric then you should not store a string '' in that column, store 0 or NULL but not a string value.

madbaho
Starting Member

8 Posts

Posted - 2011-09-27 : 12:03:44

I tried that... I'll keep playing with it.... As far as queryMySQL it has no relevance of what it supports by name.... I created that function and i change one method within function to work with what ever database schema i work with.

UUID is a Unique universal ID- it uses mac address, timestamps, and other variables to truly make every row unique.... even if it was same file the time of day u add it would make it unique from previously... UUID is the only truly unique identifier and is recommended for professional use.

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 12:49:48

Yes, I guessed that that was what a UUID was (thanks for the explanation), but it doesn't exist in Microsoft SQL Server (well, not with the name "UUID") hence my suggestion that your MySQL question might not find people here who know how to help you, and the advice I have given you maybe useless on MySQL.

Hence you might be better off trying a forum specific to MySQL

madbaho
Starting Member

8 Posts

Posted - 2011-09-27 : 13:04:45

I'm actually using postgres.... that function i had listed is a function I wrote... I was to lazy to change mysql to pg in name so i kept referencing it... Programmers are lazy :-P

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 13:11:24

OK, then its PostgreSQL. In which case change:

"This is an MS SQL forum, you may not find people here who know about My SQL - I don't ..."

to

"This is an MS SQL forum, you may not find people here who know about PostgreSQL - I don't ..."

madbaho
Starting Member

8 Posts

Posted - 2011-09-28 : 10:37:40

SUN + ORACLE= SNORKLE

The forum said SQL team.... I haven't seen anything on MS SQL... when I think of SQL I think of the syntax that oracle, mysql, pgsql use.... but then again microsoft people are one sided... makes sense... or even better MIS peeps don't know shit is all you had to say....

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-28 : 10:50:35

The forum you're posting in is called "New to SQL Server Programming". SQL Server, not PostgreSQL. Not MySQL. SQL Server (which is a product name). The site description states:

quote:
About SQLTeam.com

I started this web site with Sean Baird in June 2000 as a place for SQL Server enthusiasts to share information and build a community. I had just started ClearData Consulting with Rick Hanson and we wanted a web site focused on SQL Server. Sean and I provided the technical skills and articles and ClearData paid the bills.

Go ask about SQL Server on an Oracle or MySQL forum and see what kind of replies you get (on the Oracle forum they'll be a lot ruder than you got here).

Microsoft people are no more one-sided than Oracle people are, or MySQL people. We don't know the other products well because we specialise on one. Same as Oracle specialists, same as MySQL specialists, etc.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-09-28 : 18:34:23

The O/P has been mixing up PostgreSQL and MySQL syntax on DevShed too ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources