I have lots of requests, but they always have the same WHERE
, I done request to copy a character by typing his name from a table, so it copy the data from other tables (sometimes need to delete and copy, sometimes only update), but if I want to copy someone’s character, I have to type the names 7 times in total, and I wish I could type it once and it change for all
Here are the different requests:
request to delete all rows from “samymewtu” and insert all rows by copying someone else, here “myths”
DELETE i
FROM items i
JOIN characters c ON c.characterId = i.characterId
WHERE c.name = 'samymewtu' --Delete the inventory where you are going to paste
INSERT INTO ITEMS (CHARACTERID, ITEMID, AMOUNT, SORT, EQUIPSLOT, MAXUPGRADE, CURRENTUPGRADE, STATMOD, ISTRADABLE, UNIQUEID, ATTACHED)
SELECT
C2.CHARACTERID, I.ITEMID, I.AMOUNT, I.SORT, I.EQUIPSLOT,
I.MAXUPGRADE, I.CURRENTUPGRADE, I.STATMOD, I.ISTRADABLE, I.UNIQUEID, I.ATTACHED
FROM
ITEMS I
INNER JOIN
CHARACTERS C ON I.CHARACTERID = C.CHARACTERID
AND C.NAME = 'Myths' --The character you want to copy his inventory
INNER JOIN
CHARACTERS C2 ON C2.NAME = 'samymewtu' --The character copying the inventory of the character above
Here is the same but for the bank
DELETE i
FROM bankItems i
JOIN characters c ON c.accountId = i.accountId
WHERE c.name = 'samymewtu' --Delete the inventory where you are going to paste
INSERT INTO bankItems (accountId, ITEMID, AMOUNT, SORT, MAXUPGRADE, CURRENTUPGRADE, STATMOD, ISTRADABLE, UNIQUEID, ATTACHED)
SELECT
C2.accountId, I.ITEMID, I.AMOUNT, I.SORT, I.MAXUPGRADE,
I.CURRENTUPGRADE, I.STATMOD, I.ISTRADABLE, I.UNIQUEID, I.ATTACHED
FROM
bankItems I
INNER JOIN
CHARACTERS C ON I.accountId = C.accountId
AND C.NAME = 'Myths' --The character you want to copy his bank
INNER JOIN
CHARACTERS C2 ON C2.NAME = 'samymewtu' --The character copying the bank of the character above
Here is to copy stats, but it update the row since there is only one and I keep the characterId
UPDATE s1 SET
s1.exp=s2.exp,
s1.maxExp=s2.maxExp,
s1.totalExp=s2.totalExp,
s1.hp=s2.hp,
s1.maxHP=s2.maxHP,
s1.mp=s2.mp,
s1.maxMP=s2.maxMP,
s1.str=s2.str,
s1.def=s2.def,
s1.mag=s2.mag,
s1.dex=s2.dex,
s1.statByBonus=s2.statByBonus,
s1.usedStat=s2.usedStat,
s1.vitalityByLevel=s2.vitalityByLevel,
s1.vitalityByBonus=s2.vitalityByBonus,
s1.usedVitality=s2.usedVitality,
s1.spellByLevel=s2.spellByLevel,
s1.spellByBonus=s2.spellByBonus,
s1.usedSpell=s2.usedSpell,
s1.spellCapacity=s2.spellCapacity
FROM stats s1
CROSS JOIN stats s2
INNER JOIN characters c1 ON c1.characterId=s1.characterId
INNER JOIN characters c2 ON c2.characterId=s2.characterId
WHERE c1.name='samymewtu' --Character who must copy stats
AND c2.name='myths' -- The character from who you copy stats
There are some others but I think you get the idea, everytime I need to enter the c1.name=’samymewtu’ and the c2.name=’myths’
Advertisement
Answer
In SQL Server, you can use variables make a scriptquery dynamic.
DECLARE @C1 VARCHAR(100)
DECLARE @C2 VARCHAR(100)
SET @C1 = 'Myths'
SET @C2 = 'samymewtu'
DELETE i FROM items i
JOIN characters c ON c.characterId=i.characterId
WHERE c.name = @C2 --Delete the inventory where you are going to paste
INSERT INTO ITEMS (CHARACTERID,ITEMID,AMOUNT,SORT,EQUIPSLOT,MAXUPGRADE,CURRENTUPGRADE,STATMOD,ISTRADABLE, UNIQUEID, ATTACHED)
SELECT C2.CHARACTERID,I.ITEMID,I.AMOUNT,I.SORT,I.EQUIPSLOT,I.MAXUPGRADE,I.CURRENTUPGRADE,I.STATMOD,I.ISTRADABLE,I.UNIQUEID,I.ATTACHED
FROM ITEMS I INNER JOIN CHARACTERS C
ON I.CHARACTERID = C.CHARACTERID AND C.NAME = @C1 --The character you want to copy his inventory
INNER JOIN CHARACTERS C2 ON C2.NAME = @C2 --The character copying the inventory of the character above