[krum110487]

Ok, I do know SQL, but not well enough to know if this is even possible...


I have no code at the moment, but I have been looking around.

My Problem:
I have an access database which I am using SQL to query/update

I can connect, I can pull info just fine, and I know how to update, but I have a specific request!

I was wondering if it is possible to do a sort of condition using unique

INSERT INTO part_info (DateValue, Part_Num)
VALUES (value, value2)
WHERE DateValue=Unique Or Part_Num=Unique

I am sure this isn't correct syntax, but I just wanted to give an idea of what I am looking for. I want to insert a new part if it is a different date OR a different part number. (both make the parts unique). Otherwise I want to UPDATE the existing part if the date and the part Number are the same. (this one is not as hard).

BUT could I use them within the same SQL call so that I don't have to query the database and make my own conditional?

[xerxes333]

try something like

if exists(select * from part_info where dateValue='###' and Part_Num='###')
	/*update stmnt*/
else
	/*insert stmnt*/

This post has been edited by xerxes333: 25 March 2009 - 01:35 PM

[krum110487]

xerxes333, on 25 Mar, 2009 - 12:34 PM, said:

try something like

if exists(select * from part_info where dateValue='###' and Part_Num='###')
	/*update stmnt*/
else
	/*insert stmnt*/

Wow I had no idea you could do if statements within SQL, that is neat!, I can't believe I didn't find information on this...in hind site I feel dumb!

haha thanks a bunch, it's just what I needed!

[krum110487]

I have a whole other problem, BUT with the same issue, the if statement is fine, but I have thousands of file updates to do, is there ANY possible way to keep the updates on the SQL side and NOT between the two?

so what I need in a nutshell is...

A loop that goes through all of the updates part_info and dateValue and checks the loop and does accordingly.

otherwise I would have to make a loop within the program and that would be a bunch of connections back and forward...

I have looked online on how to do loops, but I am stuck within the realms of Access 2003...(yea I know it sucks) but it's all I can use for work!

all I need is an idea of THE best way to do this with the least amount of connections, I have looked everywhere!

Pseudo Code:

I = Number of updates

For A = 0 to I
	 if exists(select * from A.part_info where A.dateValue='###' and Part_Num='###')
		  /*A update stmnt*/
	 else
		  /*A insert stmnt*/ 
Next A

This post has been edited by krum110487: 30 March 2009 - 07:09 AM

[baavgai]

Access? Really? Ok, not an RDBMS, but this should work...

This is my standard procedure for any kind of bulk load where the table I'm loading needs to maintain it's current data. Basically you get all the data into the database first, then do all the manipulations there.

Create a table in the database for batching. The table should have a field(s) to store the primary key for the table you're updating. That's prep work, leave it there forever. On most databases you can use some kind of temp mechanism for this, but you don't have that luxury.

Now, load all the change data into the batch table.

Next, update the batch table with the PK from the table you're about to update.
e.g.

update BatchLoad
   set ID = (select ID from Victim where BatchLoad.dateValue=Victim.dataValue and BatchLoad.Part_Num=Victim.Part_Num)

That SQL should work on most. There are cleaner ways to do that, depending on your database. SQL Server has some real nice update syntax. I don't know about Access anymore.

Now it's simple, everything with an ID that's null is an insert, otherwise update.

Don't forget to clear the batch table when you're done.

[krum110487]

ok so if I understand correctly I will load all the data into the database, then compute...where would I use an inner join because my DateValue points to another table, would I use innerjoin somewhere, or is there an easier way?

[krum110487]

Ok I have this so far...

UPDATE BatchTable
	 SET ID = EXISTS(
		  SELECT PartInfo.xPid
		  FROM PartInfo
		  INNER JOIN DateTable
		  ON DateTable.Didx = PartInfo.Did
		  WHERE BatchTable.DateValue = DateTable.MonthYear AND PartInfo.PartNum = BatchTable.PartNum);

but access gives me an error "Operation must use an updatable query"

and if I take the Select statement out, it seems to still not know what values are what on the BatchTable. (It gives me a prompt to input values)

if it did get the right values it would be working, I can see why it is not getting the values, do I need to Inner Join again or what?

Thanks for the help, I am slow with SQL...

[baavgai]

Get rid of the EXISTS and see how far you get.

Bedsides, if it doesn't exist it will just return a null, which is fine.

UPDATE BatchTable
	SET ID = (
		SELECT PartInfo.xPid
			FROM PartInfo
				INNER JOIN DateTable
					ON DateTable.Didx = PartInfo.Did
			WHERE BatchTable.DateValue = DateTable.MonthYear 
				AND PartInfo.PartNum = BatchTable.PartNum
		)

This post has been edited by baavgai: 31 March 2009 - 09:05 AM

[kzimmerm]

baavgai, on 31 Mar, 2009 - 08:04 AM, said:

Get rid of the EXISTS and see how far you get.

Bedsides, if it doesn't exist it will just return a null, which is fine.

UPDATE BatchTable
	SET ID = (
		SELECT PartInfo.xPid
			FROM PartInfo
				INNER JOIN DateTable
					ON DateTable.Didx = PartInfo.Did
			WHERE BatchTable.DateValue = DateTable.MonthYear 
				AND PartInfo.PartNum = BatchTable.PartNum
		)

You can place the ISNULL function in front of your select to default a null value to something besides null:

UPDATE BatchTable
	SET ID = isnull(
		SELECT PartInfo.xPid
			FROM PartInfo
				INNER JOIN DateTable
					ON DateTable.Didx = PartInfo.Did
			WHERE BatchTable.DateValue = DateTable.MonthYear 
				AND PartInfo.PartNum = BatchTable.PartNum
		),-1)

If you are not accustom to working with nulls, which could be a pain until you are aware the behavior of working with nulls.

Just an FYI.

Kurt

[krum110487]

Access doesn't like that one bit...I believe I have to make it an IIF statement instead, but the problem is that it ISN'T Null and in this case it should find something...

@baavgai
Thats how I had it before, and it gave me the same error.

It's like it doesn't know what ANY datevalue or PartNum is within the BatchTable...

This post has been edited by krum110487: 31 March 2009 - 09:40 AM

[krum110487]

If anyone is interested, I ended up using a module within access 2003 to do my loop.

It looks something like this:

Option Compare Database

Public Sub CheckVals()
	Dim rs1 As ADODB.Recordset
	Dim rs2 As ADODB.Recordset
	Set rs1 = CurrentProject.Connection.Execute("SELECT PartInfo.Nid, PartInfo.PartNum, DateTable.MonthYear FROM PartInfo INNER JOIN DateTable ON DateTable.Didx = PartInfo.Did")
	Set rs2 = CurrentProject.Connection.Execute("SELECT Nid, PartNum, MonthYear FROM BatchTable")


		While Not rs1.EOF
			If rs1.Fields(0).Value = rs2.Fields(0).Value And rs1.Fields(1).Value = rs2.Fields(1).Value And rs1.Fields(2).Value = rs2.Fields(2).Value Then
				'Update record
			Else
				'Insert new record
			End If
			
			rs1.MoveNext
		Wend
End Sub

so within my VB code, I will send my values to "BatchTable" table and then I will call CheckVals() to do my update/create new record.

then I will clear the BatchTable, which I am sure I will do within the module on access 2003.

this way I make 1 call to the database and I access it VERY shortly to run my module then close out, let it do all the work.

I am still in the process of implementing this, but it seems like it should work correctly.