On Wednesday 25 June 2003 03:04 pm, scott.marlowe wrote: > Just wrap it in a transaction: > > begin; > select * from table where somefield='somevalue'; > (in php code) > if pg_num_rows>1... > update table set field=value where somefield=somevalue; > else > insert into table (field) values (value); > commit;
Yes, but I don't see how this is more efficient than what I said previously (??) Thanks though. RDB > On Wed, 25 Jun 2003, Reuben D. Budiardja wrote: > > Hi, > > I am developing application with PHP as the front end, PGSQL as the > > backend. I am trying to figure out what's the best way to do this. > > I want to check if an entry already exists in the table. If it does, then > > I will do > > UPDATE tablename .... > > > > otherwise, I will do > > INSER INTO tablename... > > > > What's the best way to do that? I can of course check first, and then put > > the login in PHP code, eg: > > > > // check if entry already exists > > SELECT COUNT(*) FROM tablename WHERE [cond] > > .. > > if($count >0) > > UPDATE > > else > > INSERT > > > > but this will double the hit to the database server, because for every > > operation I need to do SELECT COUNT(*) first. The data itself is not a > > lot, and the condition is not complex, but the hitting frequency is a > > lot. > > > > I vaguely remember in Oracle, there is something like this: > > > > INSERT INTO mytable > > SELECT 'value1', 'value2' > > FROM dummy_table > > WHERE NOT EXISTS > > (SELECT NULL FROM mytable > > WHERE mycondition) > > > > This query will do INSERT, if there is not an entry already in the TABLE > > mytable that match the condition mycondition. Otherwise, the INSERT just > > fails and return 0 (without returning error), so I can check on that and > > do update instead. > > > > This is especially useful in my case because about most of the time the > > INSERT will succeed, and thus will reduce the hit frequency to the DB > > server from PHP by probably a factor of 1.5 or so. > > > > Is there anything like that with PostgreSQL? I looked the docs and > > googled but haven't found anything. > > > > Anyhelp is greatly appreciated. Thanks. > > > > RDB > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN ------------------------------------------------- /"\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ ------------------------------------------------- Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. ------------------------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
