SQL query question

Zipppy

Ok ages since I used SQL so I'm 'rusty'....help me out here please :rolleyes:

Have a table with:
VenueID and SymbolD

Each venue can have multiple SymbolIDs so there are multiple records for most venues as they have more than one Symbol.

I want to run a query that will add an extra record to those venues that have a symbol = 2 (remember they will also have other symbolID records)

I want to add the SymbolID =2 to those that have a symbolID =3

Does this make sense???

So I'm planning:::

INSERT INTO VenueSymbolTable (SymbolID) value (3) WHERE SymbolID = 2;


Will that work ??? Will it create new records for me?

Cheers !!

Malice

Can you provide a bit more information about the database table layout? Are you saying it's just two columns like this or is it more complex?
[PHP]VenueSymbolTable

---

VenueID int
SymbolID int[/PHP]

Regardless of that, your intention's not quite clear from your post. Are you looking to add new rows to the table or update existing rows?

Zipppy

Malice wrote: »

Can you provide a bit more information about the database table layout? Are you saying it's just two columns like this or is it more complex?
[PHP]VenueSymbolTable

---

VenueID int
SymbolID int[/PHP]Regardless of that, your intention's not quite clear from your post. Are you looking to add new rows to the table or update existing rows?

Hi

Just those two columns plus a 'DateUpdated' field.

Each venue has multiple rows as each symbol it has is an extra record.

What is want to do is an an extra record (new row) containing venie ID and symbolID (which wil lbe 3) for every venue that has a symbolID of 2 (it will have others also)

Malice

Okay, I'm still a bit confused but that's probably just me .
Assuming you're using SQL Server then the following should work (I tested it on SQL Server 2008):

Create the table
[PHP]IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VenueSymbolTable]') AND type in (N'U'))
DROP TABLE [dbo].[VenueSymbolTable]
GO

USE <Your Database>
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[VenueSymbolTable](
[VenueID] [int] NOT NULL,
[SymbolID] [int] NOT NULL,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]

GO
[/PHP]

Add some test data
[PHP]-- Venue 1 has 1,2,3
INSERT INTO VenueSymbolTable
VALUES(1, 1, GETDATE())
INSERT INTO VenueSymbolTable
VALUES(1, 2, GETDATE())
INSERT INTO VenueSymbolTable
VALUES(1, 3, GETDATE())

-- Venue 2 has 1,2
INSERT INTO VenueSymbolTable
VALUES(2, 1, GETDATE())
INSERT INTO VenueSymbolTable
VALUES(2, 2, GETDATE())[/PHP]

Perform the insert
[PHP]BEGIN TRANSACTION test
SELECT * FROM VenueSymbolTable
DECLARE @venue int;
DECLARE TableUpdate CURSOR FOR
SELECT VenueID
FROM VenueSymbolTable
WHERE SymbolID = 2;

OPEN TableUpdate;

FETCH NEXT FROM TableUpdate INTO @venue;
@FETCH_STATUS = 0)
BEGIN
INSERT INTO VenueSymbolTable
VALUES(@venue, 3, GETDATE())
FETCH NEXT FROM TableUpdate INTO @venue;
END
CLOSE TableUpdate;
DEALLOCATE TableUpdate;

SELECT * FROM VenueSymbolTable
ROLLBACK[/PHP]
If you run the last block as-is you should see the table with five rows and then the table with seven rows, the extra two rows being added because there are two rows that have a SymbolID of 2 per the test data.

Giblet

Jesus Christ Malice :P

insert into venuesymboltable
select venueid,3,getdate() from venuesymboltable where symbolid = 2

Zipppy

Giblet wrote: »

Jesus Christ Malice :P

insert into venuesymboltable
select venueid,3,getdate() from venuesymboltable where symbolid = 2

This seems easier than Malice's version .......... but a big thanks to Malice

Freddio

:cool:

Malice

Giblet wrote: »

Jesus Christ Malice :P

insert into venuesymboltable
select venueid,3,getdate() from venuesymboltable where symbolid = 2

Hmm, I can't remember what I wrote but I did have something similar to the above written initially which didn't work.

Giblet

Malice wrote: »

Hmm, I can't remember what I wrote but I did have something similar to the above written initially which didn't work.

I know how it is, you break it down logically and think "hrmm I need to do something for each row, AH a cursor!"

Happens a lot!

Malice

Giblet wrote: »

I know how it is, you break it down logically and think "hrmm I need to do something for each row, AH a cursor!"

Happens a lot!

Yeah, it's the set-based versus procedural mindset. As soon as I started down the road of "for each row with a symbol of 2, grab the row details and insert a symbol of 3 instead" I was doomed .

Zipppy

Ok Guys here is an actual screen shot from table...

Any record (VenueID) with a 2 value SymbolID I want to add a extra/new record with a SymbolID of 3 >>>>>

Giblet

insert into venuesymboltable
select venueid,3,getdate(),'System' from venuesymboltable where symbolid = 2

Zipppy

Cheers

Ran this query ::

insert into dbo.VenueSymbol
select venueid,3,getdate(),'System' from dbo.VenueSymbol where symbolid = 2

Following error generated

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_VenueSymbol_1'. Cannot insert duplicate key in object 'dbo.VenueSymbol'.
The statement has been terminated.

Giblet

You have a composite key?

insert into venuesymboltable
select venueid,3,getdate(),'System' from venuesymboltable where symbolid = 2 and venueid not in (select venueid from venuesymboltable where symbolid = 3)

Zipppy

Giblet wrote: »

You have a composite key?

insert into venuesymboltable
select venueid,3,getdate(),'System' from venuesymboltable where symbolid = 2 and venueid not in (select venueid from venuesymboltable where symbolid = 3)

That worked a treat....take a bow

Well done boards.ie

Tar.Aldarion

Giblet wrote: »

Jesus Christ Malice :P

insert into venuesymboltable
select venueid,3,getdate() from venuesymboltable where symbolid = 2

/removes coffee from screen